CODEWITHSUNDEEP
oracle11g
acadia
acadia

Reputation: 2625

Update Oracle table with values from another table

I have two tables ORDERS and ORDER_TEMP and both are identical in structure. ORDER_ID is the primary key in both the tables.

Now, I have to Update KEY_ID column in ORDER table from the value from ORDER_TEMP table.

There are over million records in both the tables.

UPDATE ORDER  
SET KEY_ID = ( SELECT KEY_ID FROM ORDER_TEMP WHERE  ORDER_TEMP.ORDER_ID = ORDER.ORDER_ID and ORDER_TEMP.PNP_GROUP=99)

If i put a where clause in on the ORDER table and run it for one order it did work but when I did for the entire table it was running forever and it didn't work.

Please help

Upvotes: 0

Views: 2523

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

Assuming your intention is to update only those rows in the ORDER table where there is a match in the ORDER_TEMP table and where the KEY_ID is different, and ignoring the fact that ORDER is an invalid table name, I would start with ensuring that you only update the fewer than 20% of the rows in the table that you expect to modify

UPDATE ORDER  
   SET KEY_ID = ( SELECT KEY_ID 
                    FROM ORDER_TEMP 
                   WHERE ORDER_TEMP.ORDER_ID = ORDER.ORDER_ID 
                     and ORDER_TEMP.PNP_GROUP=99) 
 WHERE EXISTS( SELECT 1
                 FROM order_temp
                WHERE ORDER_TEMP.ORDER_ID = ORDER.ORDER_ID 
                  and ORDER_TEMP.PNP_GROUP=99
                  and order_temp.key_id != order.key_id)

If KEY_ID can be NULL in either table, you'd need to adjust the inequality comparison in the EXISTS clause.

If that doesn't resolve the problem, we'll need more information starting with the current query plan and the indexes that are available.

Upvotes: 1

Related Questions