Re: SQL "update" question

Greg Powers wrote: > Ok, I need to update several fields in a table using values from > another table based on: > 1) equality of TWO fields in each table AND > 2) the value in the second table is not null. > > In pig english, something like: > Update table1 > set table1.age, table1.sex, table1.income = > table2.age, table2.sex, table2.income > where table1.firstname = table2.firstname > and table1.lastname = table2.lastname > (but only do the update if the corresponding field in table2 is not > null, otherwise leave the original value in table1.(age,sex,income) > on a field by field basis. > > Obviously, I've left out the selects. > > Can anyone give me the most efficient way (in SQL) to accomplish this? I dunno about efficiency, but this probably works and I don't really know how else to phrase it. UPDATE Table1 SET (Age, Sex, Income) = ((SELECT Age, Sex, Income FROM Table2 T2 WHERE T2.FirstName = Table1.FirstName AND T2.LastName = Table1.LastName )) WHERE EXISTS (SELECT * FROM Table2 T2 WHERE T2.FirstName = Table1.FirstName AND T2.LastName = Table1.LastName) The two correlated SELECT statements seem to be necessary. The double parentheses are also mandatory. Now, I've not dealt with the NOT NULL requirement. That will be trickier, depending on which version of the server you've got. If you have, or can be bothered to write, an NVL stored procedure (not dreadfully hard), then you can use: ((SELECT NVL(Age, Table1.Age), NVL(Sex, Table1.Sex), NVL(Income, Table1.Income) ... If you can't handle that, then you'll need to write out 7 slightly different update statements, one for each case except where Age IS NULL AND Sex IS NULL AND Income IS NULL. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h> CREATE PROCEDURE NVL(val1 VARCHAR(42), altval VARCHAR(42) DEFAULT NULL) RETURNING VARCHAR(42); IF val1 IS NOT NULL THEN RETURN val1; ELSE RETURN altval; END IF; END PROCEDURE; I seem to remember 42 as the magic number (not just the meaning of Life, the Universe, and Everything); it is the length of an INTERVAL DAY(9) TO FRACTION(5), if it is the longest non-CHAR, non-BLOB data type...maybe that's only 25 characters: what about a floating point DECIMAL(32); that needs 39 characters. Oh well, some number in the vicinity of 42 will probably do.