RE: update syntax

>}From: alexmc@biccdc.co.uk (Alex McLintock) >}Date: Thu, 16 Feb 1995 11:46:29 +0000 >}X-Informix-List-Id: <news.11581> >} >}Pretty please could someone tell me what's wrong with >}this update statement. Thanks >} >}update qprice, partmaster set qprice.qpmat = partmaster.curmat >} where ( (qprice.qpson=27) AND (qprice.qpsonitem=103) >} AND (qprice.qpqtyitem=1) >} AND (partmaster.partnum='PC0651 A') >}) >} >}I am trying to copy a field out of the partmaster table (curmat) >}into a field of the same type in qprice (qpmat). The two records >}concerned have keys partnum (a string) and for qprice >}three numbers: qpson, qpsonitem, and qpqtyitem. > >I think this does what you want... > >UPDATE Qprice > SET Qpmat = (SELECT Curmat FROM Partmaster WHERE Partnum = 'PC0651 A') > WHERE Qpson = 27 > AND Qpsonitem = 103 > AND Qpqtyitem = 1; > >}Do I have to build an IN clause to this update statement? > >No. > >}I am told by non Informix database people that the above >}statement is ANSI and should work. > >I think it is not ANSI as you can only update a single table. You cannot >list multiple tables between the UPDATE and the SET keywords. > >Yours, >Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> The standard SQL syntax for update does not permit update of two tables. an IN statement would not help The ANSI SQL form of the above requires you specify the table being updated separately, the table name is then not rerquired in the set clause. Try this: update qprice FROM partmaster set qpmat = partmaster.curmat where ( (qprice.qpson=27) AND (qprice.qpsonitem=103) AND (qprice.qpqtyitem=1) AND (partmaster.partnum='PC0651 A') ) I have often used this structure on an INGRES system, it is ANSI standard and therefoire should work on INFORMIX. +----------------------------------------------------------------+ | Phil Robinson Database design/management | | GenRad Ltd., Cheadle Hulme, Manchester, England. | | E-Mail robinson@genrad.co.uk | +----------------------------------------------------------------+