UPDATE statement

Hello all, I have a question about the UPDATE statement. I would like to update certain columns in a table (I'll refer to as table 'X') with information in the same columns from another table (I'll refer to as table 'Y'). All the examples I've seen that involve the UPDATE statement show something like the following (where the name 'age' is just an example of one of the column_names in table 'X'): ... SET age = 45 ... or ... SET age = '45' ... or ... SET age = age + 2 ... Is it possible to reference the column_name 'age' from table 'Y' to assign update information to the column_name 'age' in table 'X'? Many thanks to everyone who takes the time to reply to my question. If possible, could you also send a copy of the response to my personal email account. Trevor C. trevor_chandler@ddlinc.com -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

The following update statement will work (I was using Informix 7.21). However, keep in mind that if you are going to implement this on a large scale you should analyze the performance (optimizer plan) as these types of statements are very costly. update table_1 set table_1.age = (select table_2.age from table_2 where table_2.value = table_1.value) where table_1.criteria = 'criteria'; Another method would be to write an updateable cursor statement (assuming you have the option for a programmatic solution). Michael <the_troubled_man@my-dejanews.com> wrote in message news:7gs2n5$nvi$1@nnrp1.deja.com... > Hello all, > > I have a question about the UPDATE statement. > > I would like to update certain columns in a table (I'll refer > to as table 'X') with information in the same columns from another > table (I'll refer to as table 'Y'). All the examples I've seen > that involve the UPDATE statement show something like the > following (where the name 'age' is just an example of one of the > column_names in table 'X'): > > ... SET age = 45 ... > > or > > ... SET age = '45' ... > > or > > ... SET age = age + 2 ... > > > Is it possible to reference the column_name 'age' from table 'Y' to > assign update information to the column_name 'age' in table 'X'? > > Many thanks to everyone who takes the time to reply to my > question. If possible, could you also send a copy of the > response to my personal email account. > > Trevor C. > trevor_chandler@ddlinc.com > > > -----------== Posted via Deja News, The Discussion Network ==---------- > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Michael Goldner wrote: > The following update statement will work (I was using Informix 7.21). > However, keep in mind that if you are going to implement this on a > large scale you should analyze the performance (optimizer plan) as > these types of statements are very costly. > > update table_1 > set table_1.age = (select table_2.age from table_2 > where table_2.value = table_1.value) > where table_1.criteria = 'criteria'; This is fine as long as every row in table_1 has a matching row in table_2. However, any row in table_1 without a matching row in table_2 is set to null, which is usually not the desired result. The fix is to include a second condition in the outer where clause: update table_1 set table_1.age = (select table_2.age from table_2 where table_2.value = table_1.value) where table_1.criteria = 'criteria' AND EXISTS(SELECT * FROM Table_2 WHERE Table_2.Value = Table_1.Value); You could probably use alternative conditions to EXISTS. > Another method would be to write an updateable cursor statement > (assuming you have the option for a programmatic solution). > > Michael <the_troubled_man@my-dejanews.com> wrote: > > I have a question about the UPDATE statement. > > > > I would like to update certain columns in a table (I'll refer > > to as table 'X') with information in the same columns from another > > table (I'll refer to as table 'Y'). All the examples I've seen > > that involve the UPDATE statement show something like the > > following (where the name 'age' is just an example of one of the > > column_names in table 'X'): > > > > ... SET age = 45 ... > > > > or > > > > ... SET age = '45' ... > > > > or > > > > ... SET age = age + 2 ... > > > > > > Is it possible to reference the column_name 'age' from table 'Y' to > > assign update information to the column_name 'age' in table 'X'? > > > > Many thanks to everyone who takes the time to reply to my > > question. If possible, could you also send a copy of the > > response to my personal email account. Judging from what I read recently, other RDBMS support a non-standard variant of the UPDATE statement such as: UPDATE Table_1 FROM Table_2 SET Col1 = Table_2.Col1 WHERE ... It would be nice if Informix did so too. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>