Join problem? 3

[dukeslater]

This is a simple update, but delivering unexpected results. I need to update only the records in table1 that exist in table 2:


update table1 a

set a.code =
( select code
from table2 b
where a.custid = b.custid);

This does properly update the records that exist in both tables.

Yet for those records that do not exist in table2 the statement is replacing the code in table1 with null. Ordinarily this would suggest a join problem, but I can't see why the equal join wouldn't do what I need.

Perhaps I need to use an entirely different technique, but I'm still stumped as to why I'm getting the result that I am. Thanks in advance for your help.

 

[lewisp]

You have no WHERE clause on table1, so it will attempt to update every row even when there is no matching row in table2.

You could try

update table1 a
set a.code =
( select code
  from   table2 b
  where  a.custid = b.custid)
WHERE EXISTS (SELECT 1
              FROM   table2 c
              WHERE  c.custid = a.custid);

 

[dukeslater]

Perfect, thanks. Seems like I tried that, but obviously I had it wrong...