RE: Update statement using multiple tables

Jared-
You need to use this syntax (from the manual):

UPDATE orders SET ship_charge = (SELECT SUM(total_price) * .07 FROM
items WHERE orders.order_num = items.order_num) WHERE orders.order_num =
1001

--EEM

-----Original Message-----
From: informix-list-bounces@xxxxxxxx
[mailto:informix-list-bounces@xxxxxxxx] On Behalf Of
jared.hanks@xxxxxxxxx
Sent: Friday, March 02, 2007 4:25 PM
To: informix-list@xxxxxxxx
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@xxxxxxxx
http://www.iiug.org/mailman/listinfo/informix-list
.

Relevant Pages

  • Re: Lookup in Query
    ... UPDATE table1 ... I thought the "SET" line in my first query would ... "Tom Ellison" wrote: ... FROM table1, table2 ...
    (microsoft.public.access.queries)
  • Update statement using multiple tables
    ... I have a question, hopefully an easy one, about updating values in a ... I would like to have a query ... I thought that if i put table2 in the update clause (update table1, ...
    (comp.databases.informix)
  • Re: Distinguish between same field names in 2 tables
    ... SELECT table1.*, table2.* ... syntax: dbname.schema.table.field is understood, in the multiple dot ... How and where do I insert RD in this expression so the query will ...
    (microsoft.public.access.queries)
  • Re: Update query help
    ... Use an update query. ... BACKUP your data before attempting the following. ... UPDATE Table1 INNER JOIN Table2 ... table1 and you only need table2 on a temporary basis to update table1. ...
    (microsoft.public.access.queries)
  • Re: Selecting rows from one table that are not in the other
    ... SELECT table1.* ... LEFT OUTER JOIN table2 ... Personally, I prefer syntax 2. ... the same query plan. ...
    (comp.databases.ms-sqlserver)
Loading