Merging multiple sql update statements(Oracle)

user_6812478 0
Viewed 44

I'm not very good at sql and I've tried a few things. What would be the best way to combine these 5 update statements into a single statement considering performance of the code? Would be a great help. Thank you so much!

Code:

----------------1

Update main_table
set  a = (case
..some code..  end)
where condition_2;

----------------2

Update main_table
set  b = (case
..some code.. end)
where condition_2

----------------3

Update main_table
set  c = (select x from sec_table where conditon_1)
where condition_2

----------------4

Update main_table
set  d = (select y from sec_table where conditon_1)
where condition_2

----------------5

Update main_table
set  e = (select z from sec_table where conditon_1)
where condition_2
oracle sql performance
3 Answers

I think you can write this as:

update main_table
    set a = (case ..some code.. end), 
        b = (case ..some code.. end), 
        (c, d, e) = (select x, y, z from sec_table where conditon_1)
where condition_2
user_11440350

You can combine your update queries and use only one query like this : 

UPDATE main_table
   SET a = (case ..some code.. end) ,
       b = (case ..some code.. end) ... /*the rest of your sets*/
   where /*add your conditions*/
user_87125030

you can perform that on only one update statement. Depending on the subquery you're making on that sec_table you might even tune a little more.

update main_table set a= (case ..some code.. end), 
                      b= (case ..some code.. end), 
                      c= (select x from sec_table where conditon_1),      
                      d= (select y from sec_table where conditon_1),
                      e= (select z from sec_table where conditon_1) 
where condition_2
user_62565720
Related