CODEWITHSUNDEEP
sqloracle-databasesql-update
Jamiex304
Jamiex304

Reputation: 242

Combining X number of Update Statements in one Update Statement

I have three update statements in a script that brings in data from another table and populates TOCTASK accoring to matching C, I values.

TOCTASK is large and each statement takes a long time to run, I was wondering is there a way of combinding the 3 statements into one overall update statement

This will be done on an Oracle database

Example of the update statements:

UPDATE TOCTASK
SET TOCTASK.COL1 =
  (SELECT VM_TEMP_TOCTASK.COL1
  FROM VM_TEMP_TOCTASK
  WHERE VM_TEMP_TOCTASK.C = TOCTASK.C
  AND VM_TEMP_TOCTASK.I = TOCTASK.I
  );


UPDATE TOCTASK
SET TOCTASK.COL2 =
  (SELECT VM_TEMP_TOCTASK.COL2
  FROM VM_TEMP_TOCTASK
  WHERE VM_TEMP_TOCTASK.C = TOCTASK.C
  AND VM_TEMP_TOCTASK.I = TOCTASK.I
  );


UPDATE TOCTASK
SET TOCTASK.COL3 =
  (SELECT VM_TEMP_TOCTASK.COL3
  FROM VM_TEMP_TOCTASK
  WHERE VM_TEMP_TOCTASK.C = TOCTASK.C
  AND VM_TEMP_TOCTASK.I = TOCTASK.I
  );

Any help would be great

Upvotes: 0

Views: 31

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You can set the entire tuple at once:

UPDATE TOCTASK
    SET (COL1, COL2, COL3) =
         (SELECT tt.COL1, tt.COL2, tt.COL3
          FROM VM_TEMP_TOCTASK tt
          WHERE tt.C = TOCTASK.C AND tt.I = TOCTASK.I
         );

Note that an index on VM_TEMP_TOCTASK(C, I) would probably also help performance.

And, this code will set non-matching values to NULL. Often such a subquery is used in conjunction with EXISTS:

UPDATE TOCTASK
    SET (COL1, COL2, COL3) =
         (SELECT tt.COL1, tt.COL2, tt.COL3
          FROM VM_TEMP_TOCTASK tt
          WHERE tt.C = TOCTASK.C AND tt.I = TOCTASK.I
         );
    WHERE EXISTS (SELECT 1
                  FROM VM_TEMP_TOCTASK tt
                  WHERE tt.C = TOCTASK.C AND tt.I = TOCTASK.I
                 );

Upvotes: 2

Related Questions