Simple SQL Question from a Washed Up DBA

Randy Dingman

Simple SQL Question from a Washed Up DBA
I cannot seem to get through the fog in my brain, or find any examples on
the web for a simple UPDATE statement that updates a column of a table with
the values in a column in another table where the keys are joined. This
stuff used to be easy.

Here is what I have working but don't think it is the best way to accomplish
this . . . Please provide input if you can.

update s1.t1 t1
set t1.c2 =
(select t2.c2 from s1.t2 t2 where t1.c1 = t2.c1)
where t1.c1 in
(select t3.c1 from s1.t2 t3)
;

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Janißen

Re: Simple SQL Question from a Washed Up DBA
(in response to Randy Dingman)
Randy

You are not allowed to tag your column in the SET clause. Try:

update s1.t1 t1
set c2 =
(select t2.c2 from s1.t2 t2 where t1.c1 = t2.c1)
where t1.c1 in
(select t3.c1 from s1.t2 t3)
;

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm