How to update the records from another table

Question

I have around 88k records in another table like ac. I want to update one column of the table ac into main table like tbl. Eg- Table tbl sample records like

col1 col2 col3 col4

abc  dhj  123  ab12
def  bhv  456  ds34
ghi  hwj  789  hj46
jkl  yuh  012  ke28

In table ac sample records like

col1 col3`

cba   123
fed   456
ihg   789
lkj   012

How to update the tbl value from ac table. So that the record look like

col1 col2 col3 col4

cba  dhj  123  ab12
fed  bhv  456  ds34
ihg  hwj  789  hj46
lkj  yuh  012  ke28

Have you attempted an update or merge, and what problems did you have with your code? — Alex Poole
Why is the 4th COL1 value updated to LKJ? COL3 values differ (012 vs. 210). Should you also check for permutations? (I hope not). — Littlefoot

Alex Poole Alex Poole · Accepted Answer · 2018-03-01T10:43:27

You can either do a correlated update:

update tbl
set col1 = (select col1 from ac where ac.col3 = tbl.col3)
where exists (select col1 from ac where ac.col3 = tbl.col3);

3 rows updated.

select * from tbl;

COL1 COL2 COL3 COL4
---- ---- ---- ----
cba  dhj  123  ab12
fed  bhv  456  ds34
ihg  hwj  789  hj46
jkl  yuh  012  ke28

Or a merge:

merge into tbl
using ac
on (ac.col3 = tbl.col3)
when matched then update set tbl.col1 = ac.col1;

3 rows merged.

select * from tbl;

COL1 COL2 COL3 COL4
---- ---- ---- ----
cba  dhj  123  ab12
fed  bhv  456  ds34
ihg  hwj  789  hj46
jkl  yuh  012  ke28

In both cases the fourth row isn't affected as there is no matching record in ac, as @Littlefoot pointed out. The merge just doesn't find a match; for the update version the where exists clause prevents values being set to null if there is no matchign row to update from. If the fourth row in ac was 012 instead of 210 they would all be updated, in both versions.

How to update the records from another table

1 Answers