Update from Select

It is always time consuming and costly operation when trying to update huge datasets using an update statement inside the cursor or individual update statements, that's when the power of bulk update comes to light. 

 

SQL Server:

SQL Server update from select is pretty straight forward we can join the source table and destination table and update the destination column.

 

Script:

UPDATE

    TABLE1

SET

    TABLE1.COL1 = TABLE2.COL3,

    TABLE1.COL2 = TABLE2.COL4

FROM

    TABLE1

INNER JOIN

    TABLE2

ON

    TABLE1.ID = TABLE2.ID

 
Oracle:

In oracle we cannot directly set columns to corresponding columns instead we need to specify list of columns in "SET ()" and write the select statement with columns in the same order in which they have to be set as shown below "col1" will get data from table2.col3 and "col2" will get data from table2.col4 and one more important thing we need to do is make sure to write an exists stament as show below or it will update all the records in table1.

 

Script:

UPDATE TABLE1 T1

SET

  (COL1, COL2)

  =

  (SELECT T2.COL3, T2.COL4

  FROM TABLE2 T2

  WHERE T1.ID = T2.ID

  )

WHERE EXISTS --If we do not write an exists it will update all the records in Table1

  (

  SELECT null FROM TABLE2 T2

  where T1.ID = T2.ID

  );

  Commit;

 

Thank you for reading this article, check out my other SQL Server vs. Oracle posts.