sqloracle-databasesql-updateexiststoad

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


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:

  1. The 'STATUS' and 'STATUS_LABEL' fields do not match in both tables.
  2. The records exist in TABLE_2.
  3. 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.


Solution

  • 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);