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

"DB2 v9.1.0.2", "s070210", "U810940", and Fix Pack "2".
Running in AIX 5.3

it is correct as
there is no where clause on the update t table - so each record is updated
and for those rows that do not find a match ; null is being set
I am trying to figure out how to force a where clause on outer table for update to find only the matching records..
I do not see any simple solution for the moment
the easiest/fastest way would be a small sp that opens a cursor on t - executes the select on u and only executes the update if a match is found.

How about:

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

WHERE EXISTS
( select *
from U1 u
where t.K1 = u.K1
and u.D2 = '123' )
;

The addition of the WHERE clause worked great, thanks!

Originally I was going to use a cursor to do the update but found that a curso could only be used in a stored procedure and then found that db2 does not support native sql stored procedures. I asked around this shop but no one I spoke to had ever written one. Reviewed the available docs and they were not exactly helpful. Any suggestions as to where to get a definitive example of generating, compiling and getting a stored procedure implemented in db2 would be appreciated. FWIW the system I'm developing in is AIX 5.3, DB2 9.1.

stored procs can be written in sql procedure language and do not require C anymore
some samples over here :
http://www.ibm.com/developerworks/db...412greenstein/

Looks good , thanks!