IDS 11 'update ... from' syntax
A user reading the IDS 11.10.xC2 SQL syntax guide found documentation claiming UPDATE supports a FROM clause (e.g. UPDATE tab1 SET tab1.a = tab2.a FROM tab1, tab2, tab3 WHERE ...), but every attempt in dbaccess and in a prepared 4GL statement returned error -201 (syntax error). One reply suggested the FROM-clause form of UPDATE is an XPS feature rather than standard IDS, citing the v11 online documentation, implying the manual's wording is misleading. No definitive confirmation or working alternative is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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;
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?
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?