I have requirement to update one table column. It is as follows:
I have two tables:
TABLE1 - FIELDS:
TABLE2 - FIELDS:
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:
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.