Delete linking multiple table
Hi,
I am trying to delete from a table linking multiple tables using the "USING"
key word. But the informix server comes back saying syntax error.
delete from nav1
using nav2 n2, nav1 n1#^
# 201: A syntax error has occurred.
#
where n2.id = n1.id
I am using the informix version 9.40C2
Please let me know whether any updates are missing for this to work? Or
whether there is any other way of doing the same?
Cheers,
Naveen Naik
That syntax is not supported in Informix 9.40. You can:
delete from nav1
where nav1.id in (select id from nav2);
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Wed, Dec 8, 2010 at 6:47 AM, NAVEEN NAIK <navnaik@gmail.com> wrote:
> Hi,
>
> I am trying to delete from a table linking multiple tables using the
> "USING"
> key word. But the informix server comes back saying syntax error.
>
> delete from nav1
> using nav2 n2, nav1 n1> #^
> # 201: A syntax error has occurred.
> #
> where n2.id = n1.id
>
> I am using the informix version 9.40C2
>
> Please let me know whether any updates are missing for this to work? Or
> whether there is any other way of doing the same?
>
> Cheers,
> Naveen Naik
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--20cf3054ace347acfa0496e55ede
Hi Art, Thanks for the quick response. Is there any way by which we can have this working in the 9.40 version. Any patch? Can you please let me know which version of the Informix the syntax is valid? Thanks and regards, Naveen Naik
The USING clause in a DELETE statement is ONLY supported in Informix Extended Parallel Server (XPS or version 8.xx) it is not supported in any Informix Dynamic Server, Online, or Standard Engine versions. The correct way to do this is with a non-correlated sub-query as in the example I provided. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Dec 8, 2010 at 9:27 AM, NAVEEN NAIK <navnaik@gmail.com> wrote: > Hi Art, > > Thanks for the quick response. > > Is there any way by which we can have this working in the 9.40 version. Any > patch? > > Can you please let me know which version of the Informix the syntax is > valid? > > Thanks and regards, > Naveen Naik > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf3054ace32894e90496e87286