CODEWITHSUNDEEP
sqloracle-databasesql-updatedml
autumn
autumn

Reputation: 1

Update multiple columns where values are from different tables - Oracle

I need to update multiple columns in the same table. However, the values I need to update to are coming from different tables. I currently have the update statements separated. The problem is I have a lot of columns to update and running many update statements is causing many number of locks and performance issues.

Is there any way that I can combine them into one update statement? These are two examples from my list of update statements I have now:

  UPDATE table1 t1
     SET t1.col1 = (
            SELECT col7
              FROM table1 t1b, table2 t2
             WHERE t1b.col2 = t2.col2
               AND t1b.col3 = t2.col3
               AND t1b.col2 = t1.col2)
   WHERE t1.col4 = 0
     AND t1.col2 IN (SELECT col2 FROM table2);


  UPDATE table1 t1
     SET t1.col5 = (
            SELECT col6
              FROM table1 t1c, table3 t3
             WHERE t1c.col2 = t3.col2
               AND t1c.col3 = t3.col3
               AND t1c.col2 = t1.col2 )
   WHERE t1.col4 = 0
     AND t1.col2 IN (SELECT col2 FROM table3);

Upvotes: 0

Views: 658

Answers (1)

user330315
user330315

Reputation:

A MERGE should do this:

merge into table1 tg
using (
  SELECT t1.col2, t1.col3, 
         col7 
         col6
  FROM table1 t1
    JOIN table2 t2 ON t1.col2 = t2.col2
                  AND t1.col3 = t2.col3
    JOIN table3 t3 ON t1.col2 = t3.col2
                  AND t1.col3 = t3.col3
) x ON (x.col2 = tg.col2 and x.col3 = tg.col3)
when matched then update
   set col1 = x.col7, 
       col5 = x.col6;

Note that this is slightly different to your statements as it assumes that both join to table2 and table3 are successful. If that is not the case change the (inner) joins to left (outer) joins.

Upvotes: 1

Related Questions