UPDATE updating too much

[mad0dog]

I have two tables the main table, T1, has 1+ million rows and another table U1 with a few hundred rows. T1 has a primary key, K1 and a data column D1. Table U1 has updates to be applied to T1 and has columns K1 and D1 defined identically, except that U1 has an additional column D2 and the primary key is the combination of K1 and D2.

Basically the task is to update T1.D1 for every match T1.K1 = U1.K1 where T1.D2 is some fixed value. To do this I wrote a correlated update query as:

update T1 t
set t.D1 = ( select u.D1
from U1 u
where t.K1 = u.K1
and u.D2 = '123'
);

Running the query results in every row in T1 meeting the condition t.K1 = u.K1 with the appropriate value from U1. That's the good news. The bad news is that every other row in T1, those not meeting the condition t.K1 = u.K1, has the value of D1 now set to NULL!

I am at a total loss as to why the NULLs are being set or what to do about it. Every variation of the query I can think of either does the same or fails syntactically.

Any thoughts/suggestions on this are greatly appreciated.

Robert

 

[Truusvlugindewind]

You forgot the WHERE clause, so every row got updated.

 

[Truusvlugindewind]

Try somthing like:

update T1 t
set t.D1 = 
( select u.D1
             from U1 u
             where t.K1 = u.K1
               and u.D2 = '123'
)
where exists
( select 1 
             from U1 u
             where t.K1 = u.K1
               and u.D2 = '123'
)
;

if that works, you might consider optimize using the new "common table expression" syntax because you've code the same query twice.

 

[mad0dog]

Your suggestion worked great, thanks!

I'm not familiar with "common table expression" so I'm off to google...

Thanks for you assistance.

 

[Truusvlugindewind]

Download this guy's cookbook:

http://mysite.verizon.net/Graeme_Birchall/id1.html

It's great.

 

[mad0dog]

Downloaded it and took a quick look, very impressive.
Thanks!