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.

Negative Parameters in Oracle Functions

I don’t see a lot of example of using negative values in Oracle functions. I am sure seasoned Oracle professionals are well aware of this functionalty, but are beginners?

Did you know when you use the function ADD_MONTHS you can pass in a negative number?

select add_months('01-Aug-03', 3) from dual;

would return ‘01-Nov-03′, however

select add_months('01-Aug-03', -3) from dual;

would return ‘01-May-03′

There many built-in Oracle functions that support negative values. Try SUBSTR and see what the results are.