Update statement using multiple tables

I have a question, hopefully an easy one, about updating values in a table using values from another table. I would like to have a query do something like this... update table1 set table1.columna = table2.columna, table1.columnb where table1.columnc = table2.columnc and (table1.columna <> table2.columna or table1.columnb <> table2.columnb) but when I do this I get: 217: Column (table2) not found in any table in the query (or SLV is undefined). I thought that if i put table2 in the update clause (update table1, table2) it would work, but then I get a syntax error. I am pretty confident this can be done, and i just don't have the correct sql, but can't figure it out. Any help is appreciated. Thanks, Jared

Jared- You need to use this syntax (from the manual): UPDATE orders SET ship_charge = (SELECT SUM(total_price) * .07 FROMitems WHERE orders.order_num = items.order_num) WHERE orders.order_num = 1001 --EEM -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of jared.hanks@gmail.com Sent: Friday, March 02, 2007 4:25 PM To: informix-list@iiug.org Subject: Update statement using multiple tables I have a question, hopefully an easy one, about updating values in a table using values from another table. I would like to have a query do something like this... update table1 set table1.columna = table2.columna, table1.columnb where table1.columnc = table2.columnc and (table1.columna <> table2.columna or table1.columnb <> table2.columnb) but when I do this I get: 217: Column (table2) not found in any table in the query (or SLV is undefined). I thought that if i put table2 in the update clause (update table1, table2) it would work, but then I get a syntax error. I am pretty confident this can be done, and i just don't have the correct sql, but can't figure it out. Any help is appreciated. Thanks, Jared _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list