Re: BAD UPDATE STATEMENT
zackary.evans@gmail.com wrote: > 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. <SNIP> >>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. <SNIP> > > > - 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. > Marco is entirely correct. Here's the reference, from the top of the UPDATE syntax page in the PDF version (page 2-639 in the PDF and hard copy versions of the manual). Looks like there is a documentation bug in the conversion to the HTML version. See the note (8) next to the 'Subset of FROM Clause' entry? The note 8. below says : Where Options: |--+-+--------------------------------+--+---------------------------+-+--| | | (8) (9)| | (10) | | | '-| Subset of FROM Clause |------' '-WHERE--| Condition |------' | | (6) (7) | '---------------WHERE CURRENT OFcursor_id---------------------------' Notes: ... 6. ESQL/C only 7. Stored Procedure Language only 8. Extended Parallel Server only 9. See page 2-648 10. See page 4-5 I knew there was a reason I preferred the PDF version. Actually I prefer the OLD Informix manuals. The syntax diagrams were easier to read and those version specific features would have had a note in the margin alongside the feature. If Informix's manuals kept winning accolades (and IB awards) over the years and IBM's did not, why in the name of all that's relational did they change them? Ahh well. Art S. Kagel
On Thu, 15 Jun 2006 13:37:43 -0400, "Art S. Kagel" <kagel@bloomberg.net> wrote: >zackary.evans@gmail.com wrote: >> 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. ><SNIP> >>>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. ><SNIP> >> >> >> - 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. >> > >Marco is entirely correct. Here's the reference, from the top of the >UPDATE syntax page in the PDF version (page 2-639 in the PDF and hard copy >versions of the manual). Looks like there is a documentation bug in the >conversion to the HTML version. See the note (8) next to the 'Subset of >FROM Clause' entry? The note 8. below says : > >Where Options: > >|--+-+--------------------------------+--+---------------------------+-+--| > | | (8) (9)| | (10) | | > | '-| Subset of FROM Clause |------' '-WHERE--| Condition |------' | > | (6) (7) | > '---------------WHERE CURRENT OFcursor_id---------------------------' > > >Notes: >... > 6. ESQL/C only > 7. Stored Procedure Language only > 8. Extended Parallel Server only > 9. See page 2-648 > 10. See page 4-5 > >I knew there was a reason I preferred the PDF version. Actually I prefer >the OLD Informix manuals. The syntax diagrams were easier to read and those >version specific features would have had a note in the margin alongside the >feature. If Informix's manuals kept winning accolades (and IB awards) over >the years and IBM's did not, why in the name of all that's relational did >they change them? Ahh well. Resistance is futile . . . . you will be assimilated . .. .8-) JWC