RE: updating a table using another table

Update tableB set tableB.fieldA = (select tableA.fieldA from tableA where tableA.fieldB = tableB.fieldB) > -----Original Message----- > From: Justin Tsui [SMTP:justin_tsui@worldnet.att.net] > Sent: Thursday, September 16, 1999 10:26 AM > To: informix-list@iiug.org > Subject: updating a table using another table > > Is there a way to do this??? > > update tableB set tableB.fieldA = tableA.fieldA where tableA.fieldB => tableB.fieldB >

William Raper wrote: > > Update tableB > set tableB.fieldA = (select tableA.fieldA from tableA > where tableA.fieldB = tableB.fieldB) > > > -----Original Message----- > > From: Justin Tsui [SMTP:justin_tsui@worldnet.att.net] > > Sent: Thursday, September 16, 1999 10:26 AM > > > > Is there a way to do this??? > > > > update tableB set tableB.fieldA = tableA.fieldA where tableA.fieldB => > tableB.fieldB The only snag with William's solution is that it sets to null all the TableB.FieldA values when there is no row in TableA with TableA.FieldB equal to TableB.FieldB. Since this is seldom the desired result, you have to write: UPDATE TableB SET TableB.FieldA = (SELECT TableA.FieldA FROM TableA WHERE TableA.FieldB = TableB.FieldB) WHERE TableB.FieldB IN (SELECT TableA.FieldB FROM TableA) This happens to be a relatively simple case; if you have filter conditions on the SELECT in the SET clause, then you need to repeat those filter conditions in the WHERE clause of the UPDATE. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN #include <disclaimer.h>