View previous topic :: View next topic
|
Author |
Message |
kudamala
New User
Joined: 12 Sep 2008 Posts: 51 Location: Bangalore
|
|
|
|
Hi Friends
I have requirement to update one table column. It is as follows:
I have two tables:
TABLE1 - FIELDS:
VEN_LIC
AREA_COD
GRADE
STATUS
TABLE2 - FIELDS:
AREA_COD
VEND_CODE
STATUS
Here only VEN_LIC filed alone primary key. Remaining all are not unique or primary keys.
My requirement is to update Grade, only for specific vendor in TABLE1. But the problem is VEND_CODE is not existing in TABLE1. The common field in both the tables is only AREA_COD. I have tried to update Grade as follows:
UPDATE TABLE1
SET GRADE = 'A'
WHERE GRADE='B' AND
AREA_COD = (SELECT AREA_COD FROM TABLE2
WHERE VEND_CODE = 'VEND1'
AND STATUS = 'A')
When I execute this query, it is failing because of multiple rows are fetching for update. I have around 10000 records for this vendor to update. If I create a program and use cursor, yes I can achieve it. But it is not a regular task. It is one time task, so I need to build a query to achieve this by running this query from QMS or SPUFI.
Thanks to help on this |
|
Back to top |
|
|
ssmukul
New User
Joined: 22 Jan 2008 Posts: 19 Location: India
|
|
|
|
Hi Ravi,
Try updating using 'IN' operator.
Code: |
UPDATE TABLE1
SET GRADE = 'A'
WHERE GRADE='B' AND
AREA_COD IN (SELECT AREA_COD FROM TABLE2
WHERE VEND_CODE = 'VEND1'
AND STATUS = 'A') |
Thanks, |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Quote: |
because of multiple rows are fetching for update |
no, you are failing because your subselect is returning multiple rows.
try changing
Code: |
AREA_COD = (SELECT AREA_COD
FROM TABLE2
WHERE VEND_CODE = 'VEND1'
AND STATUS = 'A') |
to
Code: |
AREA_COD EXISTS (SELECT AREA_COD
FROM TABLE2
WHERE VEND_CODE = 'VEND1'
AND STATUS = 'A') |
and db2 has COLUMNs. fields are found in records - which are found in files. |
|
Back to top |
|
|
kudamala
New User
Joined: 12 Sep 2008 Posts: 51 Location: Bangalore
|
|
|
|
Hi Friends
Thank you all. Below query has solved my problem.
UPDATE TABLE1
SET GRADE = 'A'
WHERE GRADE='B' AND
AREA_COD IN (SELECT AREA_COD FROM TABLE2
WHERE VEND_CODE = 'VEND1'
AND STATUS = 'A')
when we use 'EXISTS' it is not filtering only for VEND1, it is updating all vendors with matching AREA_COD |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
my bad. incorect implementation of EXISTS predicate. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
Code: |
UPDATE TABLE1 A
SET GRADE = 'A'
WHERE GRADE='B' AND
EXISTS (SELECT 1
FROM TABLE2 B
WHERE VEND_CODE = 'VEND1'
AND STATUS = 'A'
and A.area_cod = b.area_cod ) |
|
|
Back to top |
|
|
|