Re: SQL Question: Update Syntax

On Mon, 14 Dec 1998, Axel Sander wrote: > I'm fiddling around with the following SQL problem: > > I have 2 tables 'table1' and 'table2' each having the columns 'flag' > and 'value'. How can I update table1.flag with the value of > table2.flag where table1.value=table2.value Untested: UPDATE Table1 SET Flag = (SELECT Flag FROM Table2 WHERE Table2.Value = Table1.Value) WHERE Value IN (SELECT Value FROM Table2); If you omit the WHERE clause of the UPDATE statement, you are likely to find nulls in the rows where Table1.Value doesn't have a matching counterpart in Table2. That's a correlated sub-query; it is not incredibly efficient, but then, neither are the alternatives. > The informix manual isn't very helpful on this problem. So send a suggestion to doc@informix.com, as it says in the manual. Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix v0.60 -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn