Re: IDS 11 'update ... from' syntax

On Fri, Mar 14, 2008 at 1:16 AM, Krishna <calvinkrishy@gmail.com> wrote: > On Mar 13, 10:39 pm, "Jim Tranny" <jimtra...@gmail.com> wrote: > > I'm reading the IDS 11.10.xC2 SQL syntax guide and we see that the > > 'update ... from' syntax is now supported (page 2-719). And page > > 2-729 states: "For Dynamic Server, the UPDATE statement supports the > > FROM clause syntax of the SELECT statement." This example is given: > > UPDATE tab1 SET tab1.a = tab2.a FROM tab1, tab2, tab3 > > WHERE tab1.b = tab2.b AND tab2.c =tab3.c;> > Work only in XPS I believe. The version 11 documentation mentions > this: > http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/com.ibm.sqls.doc/sqls925.htm > > > > > > But for the life of me I can't get this to run in dbaccess. I get > > '201: A syntax error has occurred.' > > I've even tried it in 4GL using a prepared statement, still no go. > > > > Has anyone else been able to execute such a statement? > I did see that, but have a another look at the second paragraph; it seems to indicate that IDS supports the full FROM syntax, as opposed to XPS which accepts only a subset of the FROM clause. Also, why then does the syntax diagram show that it's valid: http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/com.ibm.sqls.doc/sqls925.htm

On Mar 14, 5:33 pm, "Jim Tranny" <jimtra...@gmail.com> wrote: > On Fri, Mar 14, 2008 at 1:16 AM, Krishna <calvinkri...@gmail.com> wrote: > > On Mar 13, 10:39 pm, "Jim Tranny" <jimtra...@gmail.com> wrote: > > > I'm reading the IDS 11.10.xC2 SQL syntax guide and we see that the > > > 'update ... from' syntax is now supported (page 2-719). And page > > > 2-729 states: "For Dynamic Server, the UPDATE statement supports the > > > FROM clause syntax of the SELECT statement." This example is given: > > > UPDATE tab1 SET tab1.a = tab2.a FROM tab1, tab2, tab3 > > > WHERE tab1.b = tab2.b AND tab2.c =tab3.c;> > > Work only in XPS I believe. The version 11 documentation mentions > > this: > > http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic... > > > > But for the life of me I can't get this to run in dbaccess. I get > > > '201: A syntax error has occurred.' > > > I've even tried it in 4GL using a prepared statement, still no go. > > > > Has anyone else been able to execute such a statement? > > I did see that, but have a another look at the second paragraph; it > seems to indicate that IDS supports the full FROM syntax, as opposed > to XPS which accepts only a subset of the FROM clause. Also, why then > does the syntax diagram show that it's valid:http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic... Have seen a documentation note ($INFORMIXDIR/release/en_us/0333/ ids_sqls_docnotes_10.0.txt) that said that its supported in XPS only. That was version 10 though. ==Quote== 1.2.13 Subset of the FROM Clause in the UPDATE Statement The UPDATE statement syntax diagram correctly restricts the "Subset of FROM Clause" segment to Extended Parallel Server, but subsequent text that describes this feature omits a similar qualifier for this syntax feature, which supports a subset of the FROM clause of the SELECT statement for Extended Parallel Server only. ==End quote== Krishna