Oracle SQL update field in one table with another table using 'where' clause and 'exists' statement

Question

I have 2 tables (TABLE_1, TABLE_2) and I need to update the 'STATUS' field in TABLE_1 with the 'STATUS_LABEL' field in TABLE_2, where:

The 'STATUS' and 'STATUS_LABEL' fields do not match in both tables.
The records exist in TABLE_2.
Only to update records where the category in 'TABLE_1' is 'SPARKLA'.

I have tried this so far however when I try to run the statement in Oracle Toad, it takes ages to load and doesnt return any errors so i'm not sure if I've done something wrong or if I'm missing something:

UPDATE TABLE_1
SET TABLE_1.STATUS=(SELECT TABLE_2.STATUS_LABEL
  FROM TABLE_2
  WHERE TABLE_2.ID = TABLE_1.ID 
  AND TABLE_2.STATUS_LABEL <> TABLE_1.STATUS)
WHERE TABLE_1.CATEGORY = 'SPARKLA'
AND EXISTS (SELECT 1 FROM TABLE_2 WHERE TABLE_2.ID =TABLE_1.ID);

Please could you kindly look at my code and guide me what to do.

Thank you.

you can try a merge https://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle — nbk, Jul 20 '23 at 11:15

score 1 · Accepted Answer · answered Jul 20 '23 at 12:16

The where clause for the exists should be the same as for the select used for update so as to avoid updating to null in case that select finds nothing. Thus< I think it should be like below:

UPDATE TABLE_1
SET TABLE_1.STATUS=(SELECT TABLE_2.STATUS_LABEL
  FROM TABLE_2
  WHERE TABLE_2.ID = TABLE_1.ID 
  AND TABLE_2.STATUS_LABEL <> TABLE_1.STATUS)
WHERE TABLE_1.CATEGORY = 'SPARKLA'
AND EXISTS (SELECT 1
  FROM TABLE_2
  WHERE TABLE_2.ID = TABLE_1.ID 
  AND TABLE_2.STATUS_LABEL <> TABLE_1.STATUS);

Thank you for your answer, I have used this and it works nicely! — R Sem, Jul 21 '23 at 09:43

score 1 · Answer 2 · answered Jul 20 '23 at 12:47

I agree with @nbk comment that a merge is much more straightforward than an update when you have complex comparative conditions. You construct a merge in two steps:

Create a Select query that identifies the records you want to change and the value you want to update to.
Embed the Select into the Merge syntax.

So a simple Select with an inner join will create the transactional file you will use to update:

Select Table_1.ID, Table_2.Status_Label
From Table_1 Inner Join Table_2
  On Table_1.ID=Table_2.ID
Where Table_1.Category='SPARKLA'
  And TABLE_2.STATUS_LABEL <> TABLE_1.STATUS

Adding that into a Merge gets:

Merge Into Table_1 Using (
    Select Table_1.ID, Table_2.Status_Label
    From Table_1 Inner Join Table_2
      On Table_1.ID=Table_2.ID
    Where Table_1.Category='SPARKLA'
      And TABLE_2.STATUS_LABEL <> TABLE_1.STATUS
) U On (Table_1.ID=U.ID)
When Matched Then Update Set Table_1.Status=U.Status_Label

Thank you so much for your response! This works well for me :) — R Sem, Jul 21 '23 at 09:43

Oracle SQL update field in one table with another table using 'where' clause and 'exists' statement

2 Answers2