CODEWITHSUNDEEP
sqloracle-database
user3096487
user3096487

Reputation: 399

Oracle update query using condition from another table

I am trying to update a column in a table based on the below select statement. The column I'm updating is not join condition and is a different value.

SELECT * 
FROM TRANS DT, RECOVER RT
WHERE DT.SERIAL_NUMBER =RT.SERIAL_NUMBER
AND DT.DATE >= TO_DATE('07/14/2016','mm/dd/yyyy')

I've tried the following and get errors. Any assistance would be appreciated.

UPDATE TRANS
SET CODE = SUCCESS
WHERE (SELECT * 
       FROM TRANS DT, RECOVER RT
       WHERE DT.SERIAL_NUMBER = RT.SERIAL_NUMBER
         AND DT.DATE >= TO_DATE('07/14/2016', 'mm/dd/yyyy')

Upvotes: 0

Views: 1261

Answers (1)

vercelli
vercelli

Reputation: 4757

Since SUCCESS is a string try with Exists:

UPDATE TRANS
SET CODE = 'SUCCESS'
WHERE exists (SELECT 1
                FROM  RECOVER RT
               WHERE TRANS.SERIAL_NUMBER = RT.SERIAL_NUMBER
                 AND TRANS.DATE >= TO_DATE('07/14/2016','mm/dd/yyyy');

Upvotes: 1

Related Questions