Square DBA – Oracle DBA Talk

Update data in one table with data from another table

Have you ever need to update data in one table with data from another table? I do all the time. One way I perform the update is with functions that I built. I pass a column into the function and it returns a result that use in my update statement. This works well if I am not updating a large numbers rows. If I am updating a large number of rows this can be pretty slow. So instead I run a statement like this:

UPDATE o1dw.stg_acme a
SET (switch_id) = (SELECT switch_id from o1dw.switch_info b
WHERE b.SWITCH_IP = a.client_ip and b.SWITCH_GROUP = 'SAC')
WHERE EXISTS (SELECT 1 from o1dw.switch_info b
WHERE b.SWITCH_IP = a.client_ip and b.SWITCH_GROUP = 'SAC') 

This method is much more efficient. This update takes about 12 minutes on 3.5 million rows.