CODEWITHSUNDEEP
sqloracle-databasesql-updateleft-join
Silverfin
Silverfin

Reputation: 495

SQL Oracle Update containing left join

I want to perform an update for the results of a select query.

SELECT
    a.reason,
    n.note
FROM applications a
LEFT JOIN notes n on n.app_id = a.app_id
    AND n.note LIKE '%old%'
WHERE a.code = 'run' AND a.reason IS NULL

I thought I could perform these updates separately wrapping the select in an update however I get the error ORA-01733: virtual column not allowed here. How can I go about performing these updates?

UPDATE (
    SELECT
        a.reason AS Reason
    FROM applications a
    LEFT JOIN notes n on n.app_id = a.app_id
        AND n.note LIKE '%old%'
    WHERE a.code = 'run' AND a.reason IS NULL
) SET Reason = null

UPDATE (
    SELECT
        n.note AS Note
    FROM applications a
    LEFT JOIN notes n on n.app_id = a.app_id
        AND n.note LIKE '%old%'
    WHERE a.code = 'run' AND a.reason IS NULL
) SET Note = null

Upvotes: 0

Views: 443

Answers (1)

Popeye
Popeye

Reputation: 35920

You can't update the two tables at the same time. You need two different update statements as follows:

Updating the APPLICATIONS table is quite easy as all the records of the APPLICATIONS table having a.code = 'run' AND a.reason IS NULL will be there in your SELECT query.

UPDATE APPLICATIONS A
   SET
    REASON = NULL
 WHERE A.CODE = 'run'
   AND A.REASON IS NULL;

To update the NOTES table, you can use the EXISTS clause as follows:

UPDATE NOTES N
   SET
    NOTE = NULL
 WHERE EXISTS (
    SELECT 1
      FROM APPLICATIONS A
     WHERE N.APP_ID = A.APP_ID
       AND A.CODE = 'run'
       AND A.REASON IS NULL
)
AND N.NOTE LIKE '%old%'

You must update the NOTES table first and then APPLICATIONS table as while updating the NOTES table you are using the condition A. REASON IS NULL but while updating the APPLICATIONS table, you are updating the REASON column.

Upvotes: 1

Related Questions