BAD UPDATE STATEMENT

UPDATE list SET list.last_name = tmp.last_name FROM list INNER JOIN tmp ON list.sid = tmp.sid WHERE list.last_name <> tmp.last_name this statement works fine in SQL Server, but i get a 201 syntax error when I run it in INFORMIX what is the problem here? i can't for the life of me figure out what is wrong with this statement. thanks

On 14 Jun 2006 16:16:28 -0700, zackary.evans@gmail.com <zackary.evans@gmail.com> wrote: > UPDATE list > SET list.last_name = tmp.last_name > FROM list INNER JOIN tmp > ON list.sid = tmp.sid > WHERE list.last_name <> tmp.last_name > > this statement works fine in SQL Server, but i get a 201 syntax error > when I run it in INFORMIX > > what is the problem here? i can't for the life of me figure out what is > wrong with this statement. The syntax isn't valid in IDS - that's all. Specifically, the FROM and ON clauses are not supported. The SQL-2003 standard doesn't recognize those clauses in the UPDATE statement either. Basically, it is a non-standard (possibly Microsoft-proprietary) extension to the UPDATE statement. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

Jonathan Leffler wrote: > On 14 Jun 2006 16:16:28 -0700, zackary.evans@gmail.com > <zackary.evans@gmail.com> wrote: > > UPDATE list > > SET list.last_name = tmp.last_name > > FROM list INNER JOIN tmp > > ON list.sid = tmp.sid > > WHERE list.last_name <> tmp.last_name > > > > this statement works fine in SQL Server, but i get a 201 syntax error > > when I run it in INFORMIX > > > > what is the problem here? i can't for the life of me figure out what is > > wrong with this statement. > > The syntax isn't valid in IDS - that's all. Specifically, the FROM > and ON clauses are not supported. > > The SQL-2003 standard doesn't recognize those clauses in the UPDATE > statement either. Basically, it is a non-standard (possibly > Microsoft-proprietary) extension to the UPDATE statement. > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/ what would you replace this with then? this is a powerful update statement that performs well. the only replacement i can think of is a subquery which is really slow.

zackary.ev...@gmail.com wrote: > Jonathan Leffler wrote: > > On 14 Jun 2006 16:16:28 -0700, zackary.evans@gmail.com > > <zackary.evans@gmail.com> wrote: > > > UPDATE list > > > SET list.last_name = tmp.last_name > > > FROM list INNER JOIN tmp > > > ON list.sid = tmp.sid > > > WHERE list.last_name <> tmp.last_name > > > > > > this statement works fine in SQL Server, but i get a 201 syntax error > > > when I run it in INFORMIX > > > > > > what is the problem here? i can't for the life of me figure out what is > > > wrong with this statement. > > > > The syntax isn't valid in IDS - that's all. Specifically, the FROM > > and ON clauses are not supported. > > > > The SQL-2003 standard doesn't recognize those clauses in the UPDATE > > statement either. Basically, it is a non-standard (possibly > > Microsoft-proprietary) extension to the UPDATE statement. > > > > -- > > Jonathan Leffler #include <disclaimer.h> > > Email: jleffler@earthlink.net, jleffler@us.ibm.com > > Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/ > http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls881.htm shows the FROM clause in an update statement. official informix documentation. also this statement WILL work in informix if i change it to (proves the ON clause is okay): SELECT * FROM list INNER JOIN tmp ON list.sid = tmp.sid WHERE list.last_name <> tmp.last_name

zackary.evans@gmail.com wrote: > zackary.ev...@gmail.com wrote: > >>Jonathan Leffler wrote: >> >>>On 14 Jun 2006 16:16:28 -0700, zackary.evans@gmail.com >>><zackary.evans@gmail.com> wrote: >>> >>>> UPDATE list >>>> SET list.last_name = tmp.last_name >>>> FROM list INNER JOIN tmp >>>> ON list.sid = tmp.sid >>>> WHERE list.last_name <> tmp.last_name >>>> >>>>this statement works fine in SQL Server, but i get a 201 syntax error >>>>when I run it in INFORMIX >>>> >>>>what is the problem here? i can't for the life of me figure out what is >>>>wrong with this statement. >>> >>>The syntax isn't valid in IDS - that's all. Specifically, the FROM >>>and ON clauses are not supported. >>> >>>The SQL-2003 standard doesn't recognize those clauses in the UPDATE >>>statement either. Basically, it is a non-standard (possibly >>>Microsoft-proprietary) extension to the UPDATE statement. >>> >>>-- >>>Jonathan Leffler #include <disclaimer.h> >>>Email: jleffler@earthlink.net, jleffler@us.ibm.com >>>Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/ >> > > > http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls881.htm > > shows the FROM clause in an update statement. official informix > documentation. > > also this statement WILL work in informix if i change it to (proves the > ON clause is okay): > > SELECT * > FROM list INNER JOIN tmp > ON list.sid = tmp.sid > WHERE list.last_name <> tmp.last_name> Well, you can't cherry pick what you like from the documentation: - The syntax you are showing only applies to XPS (or, at least, it did when I last checked). Is that the engine that you are using? As a side note it was an informix extension to sql (and not an ansi aware at that) made specifically to provide a more powerful and faster alternative to cursor/update combos which on XPS don't exactly go at light speed. - The fact that the FROM clause and ansi joins are part of the SELECT syntax does not imply in any way that they are part of UPDATE too Just to throw my £0.01 (and a bit), the FROM clause issn't part of the UPDATE statement in DB2 either, and as a reply to your previous post, have you heard of subquery flattening? -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

Marco Greco wrote: > zackary.evans@gmail.com wrote: > > zackary.ev...@gmail.com wrote: > > > >>Jonathan Leffler wrote: > >> > >>>On 14 Jun 2006 16:16:28 -0700, zackary.evans@gmail.com > >>><zackary.evans@gmail.com> wrote: > >>> > >>>> UPDATE list > >>>> SET list.last_name = tmp.last_name > >>>> FROM list INNER JOIN tmp > >>>> ON list.sid = tmp.sid > >>>> WHERE list.last_name <> tmp.last_name > >>>> > >>>>this statement works fine in SQL Server, but i get a 201 syntax error > >>>>when I run it in INFORMIX > >>>> > >>>>what is the problem here? i can't for the life of me figure out what is > >>>>wrong with this statement. > >>> > >>>The syntax isn't valid in IDS - that's all. Specifically, the FROM > >>>and ON clauses are not supported. > >>> > >>>The SQL-2003 standard doesn't recognize those clauses in the UPDATE > >>>statement either. Basically, it is a non-standard (possibly > >>>Microsoft-proprietary) extension to the UPDATE statement. > >>> > >>>-- > >>>Jonathan Leffler #include <disclaimer.h> > >>>Email: jleffler@earthlink.net, jleffler@us.ibm.com > >>>Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/ > >> > > > > > > http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls881.htm > > > > shows the FROM clause in an update statement. official informix > > documentation. > > > > also this statement WILL work in informix if i change it to (proves the > > ON clause is okay): > > > > SELECT * > > FROM list INNER JOIN tmp > > ON list.sid = tmp.sid > > WHERE list.last_name <> tmp.last_name> > > > Well, you can't cherry pick what you like from the documentation: > > - The syntax you are showing only applies to XPS (or, at least, it did when I > last checked). Is that the engine that you are using? As a side note it was an > informix extension to sql (and not an ansi aware at that) made specifically to > provide a more powerful and faster alternative to cursor/update combos which > on XPS don't exactly go at light speed. > > - The fact that the FROM clause and ansi joins are part of the SELECT syntax > does not imply in any way that they are part of UPDATE too > > Just to throw my £0.01 (and a bit), the FROM clause issn't part of the UPDATE > statement in DB2 either, and as a reply to your previous post, have you heard > of subquery flattening? > > > -- > Ciao, > Marco > ______________________________________________________________________________ > Marco Greco /UK /IBM Standard disclaimers apply! > > Structured Query Scripting Language http://www.4glworks.com/sqsl.htm > 4glworks http://www.4glworks.com > Informix on Linux http://www.4glworks.com/ifmxlinux.htm - this link does not mention it only works on XPS, how am i supposed to know that? - i know that, i was saying that the "ON" statement is not the problem as was mentioned by the first reply - subquery flattening - do you mean correlated subquerys? we have tried that and the performance is less than desirable.