RE: update syntax

Phil, }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 }therefore should work on INFORMIX. Please justify your claim that the syntax quoted (with a FROM clause in the UPDATE statement) is part of an SQL standard. I don't have a complete SQL-92 standard, but the UPDATE statement above does not conform to SQL-92 given the BNF notation below (which is from Appendix G of 'Understanding the New SQL: A Complete Guide' by J Melton and A R Simon, published by Morgan Kaufmann, 1993 ISBN 0-55860-245-3): <update statement: positioned> ::= UPDATE <table name> SET <set clause> WHERE CURRENT OF <cursor name> <update statement: searched> ::= UPDATE <table name> SET <set clause> [ WHERE <search condition> ] The FROM clause in the UPDATE statement is not part of SQL-92. Therefore, I believe that if this works on Ingres, it must be an Ingres extension of the SQL standard -- choose your own year suffix for which version of the SQL standard. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> }From: robinson@genrad.co.uk (Phil Robinson) }Date: 17 Feb 95 09:26:27 GMT }X-Informix-List-Id: <news.11633> } }>}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; }>[...] }[...] }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 }therefore should work on INFORMIX.