sql syntax o update error
A user got error -201 running an "UPDATE pruebas SET a.tubo=b.tubo FROM pruebas a, pruebastor b WHERE ..." statement to copy a column between tables. Replies explained that the UPDATE...FROM multi-table syntax isn't supported by IDS — it was only ever an Extended Parallel Server (XPS) feature, documented ambiguously in older manuals (e.g. v10) and since removed from the 11.70 docs; one poster also noted you can't qualify the column in SET as "a.tubo". The working alternative given was a correlated subquery: UPDATE tableA SET col = (SELECT col FROM tableB WHERE join condition) WHERE ..., which solved the user's problem.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello:
I'm trying to execute this query to update one table from another, but ids
gime me a -201 error. I dont't know why, can anyone help me?
update pruebas set a.tubo=b.tubo FROM pruebas a, pruebastor b whereb.tubo<>'' and a.codigo=b.codigo and a.baja='3000-01-01 00:00:00' and
b.baja='3000-01-01 00:00:00' and a.clase='1' and a.tubo=''
Regards.
--0023544713e09ad79b04d4bf3926
Try following way ...: Update <table A> Set <ColumnOfTableA> = (SELECT
<ColumnOfTableB> FROM <table B> WHERE <table A.column = Table
b.column....)WHERE <table A columns > Hope this will help...
--Dharmendra> To: ids@iiug.org
> From: jfrancisco.navarro@gmail.com
> Subject: sql syntax o update error [29460]
> Date: Sat, 2 Feb 2013 10:07:59 -0500
>
> Hello:
>
> I'm trying to execute this query to update one table from another, but ids
> gime me a -201 error. I dont't know why, can anyone help me?
>
> update pruebas set a.tubo=b.tubo FROM pruebas a, pruebastor b where> b.tubo<>'' and a.codigo=b.codigo and a.baja='3000-01-01 00:00:00' and
> b.baja='3000-01-01 00:00:00' and a.clase='1' and a.tubo=''
>
> Regards.
>
> --0023544713e09ad79b04d4bf3926
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Hello:
Thank you very much, i got it but i'm a little disapointed because ibm
informix documentacion show update....from sql syntax but it doesn't work.
Regards.
El 02/02/2013 17:20, "Dharmendra Sharma" <dharmendrasharma@hotmail.com>
escribió:
> Try following way ...: Update <table A> Set <ColumnOfTableA> = (SELECT
> <ColumnOfTableB> FROM <table B> WHERE <table A.column = Table
> b.column....)WHERE <table A columns > Hope this will help...
> --Dharmendra> To: ids@iiug.org
> > From: jfrancisco.navarro@gmail.com
> > Subject: sql syntax o update error [29460]
> > Date: Sat, 2 Feb 2013 10:07:59 -0500
> >
> > Hello:
> >
> > I'm trying to execute this query to update one table from another, but
> ids
> > gime me a -201 error. I dont't know why, can anyone help me?
> >
> > update pruebas set a.tubo=b.tubo FROM pruebas a, pruebastor b where> > b.tubo<>'' and a.codigo=b.codigo and a.baja='3000-01-01 00:00:00' and
> > b.baja='3000-01-01 00:00:00' and a.clase='1' and a.tubo=''
> >
> > Regards.
> >
> > --0023544713e09ad79b04d4bf3926
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--20cf302ef940be68a604d4cf95f7
Hello: Thank you very much, i got it but i'm a little disapointed because ibm informix documentacion show update....from sql syntax but it doesn't work. Regards. El 02/02/2013 17:20, "Dharmendra Sharma" <dharmendrasharma@hotmail.com> escribió: --20cf3074b46669242704d4cf966b
On Sun, Feb 3, 2013 at 2:39 AM, Juan Francisco González Navarro < jfrancisco.navarro@gmail.com> wrote: > Thank you very much, i got it but i'm a little disapointed because IBM > Informix documentacion show update....from SQL syntax but it doesn't work. > > El 02/02/2013 17:20, "Dharmendra Sharma" <dharmendrasharma@hotmail.com> > escribió: > > --20cf3074b46669242704d4cf966b > It is documented, but if you read the documentation carefully enough, you will note that it is tagged 'XPS only', and IDS is not XPS. If you go to the IDS 11.70 Info Centre, then you'll find that the FROM option is no longer documented at all: http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.sqls.doc/ids _sqs_1254.htm If you look in the original PDF version of the 'IBM Informix: Guide to SQL Syntax' (SC27-3532-00 on the bottom of the title page), you'd find that the WHERE clause is documented slightly differently. I've not got the patience to reinstate lines, but one of the paths for the 'WHERE option' section includes 'Subset of FROM clause' with the annotation '(7)' which says 'Extended Parallel Server only' (which is what I mean by XPS). WHERE Options: (7) (8) Subset of FROM Clause WHERE condition (5) (6) WHERE CURRENT OF cursor_id Notes: 1 Informix extension and Dynamic Server only 2 See Optimizer Directives on page 5-35 3 See SET Clause on page 2-795 4 See Collection-Derived Table on page 5-4 5 ESQL/C and SPL only 6 See Using the WHERE CURRENT OF Clause (ESQL/C, SPL) on page 2-804 7 Extended Parallel Server only 8 See WHERE Clause of UPDATE on page 2-801 So, the notation was only ever documented for XPS, but that has now been removed from the (online version of) the manuals. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2013.0118 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --047d7b343b2aa649ef04d4d677d4
That's perfectly understandable... But I believe the last version where
that was present in the manual is 11.10.
It is/should be fixed in 11.50 and 11.70
Even then, for 11.10 in the detailed section it states it applies only to
Informix XPS.
In any case, it was a bug in the manual which was fixed in the mean time.
Can you confirm in which version you saw that?
Regards
On Sun, Feb 3, 2013 at 10:39 AM, Juan Francisco González Navarro <
jfrancisco.navarro@gmail.com> wrote:
> Hello:
>
> Thank you very much, i got it but i'm a little disapointed because ibm
> informix documentacion show update....from sql syntax but it doesn't work.
>
> Regards.
> El 02/02/2013 17:20, "Dharmendra Sharma" <dharmendrasharma@hotmail.com>
> escribió:
>
> > Try following way ...: Update <table A> Set <ColumnOfTableA> = (SELECT
> > <ColumnOfTableB> FROM <table B> WHERE <table A.column = Table
> > b.column....)WHERE <table A columns > Hope this will help...
> > --Dharmendra> To: ids@iiug.org
> > > From: jfrancisco.navarro@gmail.com
> > > Subject: sql syntax o update error [29460]
> > > Date: Sat, 2 Feb 2013 10:07:59 -0500
> > >
> > > Hello:
> > >
> > > I'm trying to execute this query to update one table from another, but
> > ids
> > > gime me a -201 error. I dont't know why, can anyone help me?
> > >
> > > update pruebas set a.tubo=b.tubo FROM pruebas a, pruebastor b where> > > b.tubo<>'' and a.codigo=b.codigo and a.baja='3000-01-01 00:00:00' and
> > > b.baja='3000-01-01 00:00:00' and a.clase='1' and a.tubo=''
> > >
> > > Regards.
> > >
> > > --0023544713e09ad79b04d4bf3926
> > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --20cf302ef940be68a604d4cf95f7
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--047d7b6d93407587ac04d4e7095e
Doesn't this work, though?:
UPDATE a set tubo = b.tubo
FROM pruebas a
INNER JOIN pruebastor b
ON b.codigo = a.codigo
AND b.baja= a.baja
AND b.tubo <> ''
WHERE a.clase='1'
AND a.baja='3000-01-01 00:00:00'
AND a.tubo=''
As far as it goes, shouldn't this work as well?
update a set tubo=b.tubo FROM pruebas a, pruebastor b where b.tubo<>'' anda.codigo=b.codigo and a.baja='3000-01-01 00:00:00' and
b.baja='3000-01-01 00:00:00' and a.clase='1' and a.tubo=''
I think his syntax error is the bit where it said "SET a.tubo". I'm pretty
sure you can't have the "a." bit there.
--EEM
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jonathan
Leffler
Sent: Sunday, February 03, 2013 12:52 PM
To: ids@iiug.org
Subject: Re: sql syntax o update error [29464]
On Sun, Feb 3, 2013 at 2:39 AM, Juan Francisco González Navarro <
jfrancisco.navarro@gmail.com> wrote:
> Thank you very much, i got it but i'm a little disapointed because IBM
> Informix documentacion show update....from SQL syntax but it doesn't work.
>
> El 02/02/2013 17:20, "Dharmendra Sharma"
> <dharmendrasharma@hotmail.com>
> escribió:
>
> --20cf3074b46669242704d4cf966b
>
It is documented, but if you read the documentation carefully enough, you
will note that it is tagged 'XPS only', and IDS is not XPS.
If you go to the IDS 11.70 Info Centre, then you'll find that the FROM
option is no longer documented at all:
http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.sqls.doc/ids
_sqs_1254.htm
If you look in the original PDF version of the 'IBM Informix: Guide to SQL
Syntax' (SC27-3532-00 on the bottom of the title page), you'd find that the
WHERE clause is documented slightly differently. I've not got the patience
to reinstate lines, but one of the paths for the 'WHERE option' section
includes 'Subset of FROM clause' with the annotation '(7)' which says
'Extended Parallel Server only' (which is what I mean by XPS).
WHERE Options:
(7) (8)
Subset of FROM Clause WHERE condition
(5) (6)
WHERE CURRENT OF cursor_id
Notes:
1 Informix extension and Dynamic Server only
2 See "Optimizer Directives" on page 5-35
3 See "SET Clause" on page 2-795
4 See "Collection-Derived Table" on page 5-4
5 ESQL/C and SPL only
6 See "Using the WHERE CURRENT OF Clause (ESQL/C, SPL)" on page 2-804
7 Extended Parallel Server only
8 See "WHERE Clause of UPDATE" on page 2-801
So, the notation was only ever documented for XPS, but that has now been
removed from the (online version of) the manuals.
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2013.0118 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--047d7b343b2aa649ef04d4d677d4
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
I saw this link surfering ibm documentacion web last night.
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sq
ls.doc/sqls864.htm
I've check it's for v10.
Regards.
2013/2/4 Fernando Nunes <domusonline@gmail.com>
> That's perfectly understandable... But I believe the last version where
> that was present in the manual is 11.10.
> It is/should be fixed in 11.50 and 11.70
> Even then, for 11.10 in the detailed section it states it applies only to
> Informix XPS.
>
> In any case, it was a bug in the manual which was fixed in the mean time.
> Can you confirm in which version you saw that?
> Regards
>
> On Sun, Feb 3, 2013 at 10:39 AM, Juan Francisco González Navarro <
> jfrancisco.navarro@gmail.com> wrote:
>
> > Hello:
> >
> > Thank you very much, i got it but i'm a little disapointed because ibm
> > informix documentacion show update....from sql syntax but it doesn't
> work.
> >
> > Regards.
> > El 02/02/2013 17:20, "Dharmendra Sharma" <dharmendrasharma@hotmail.com>
> > escribió:
> >
> > > Try following way ...: Update <table A> Set <ColumnOfTableA> = (SELECT
> > > <ColumnOfTableB> FROM <table B> WHERE <table A.column = Table
> > > b.column....)WHERE <table A columns > Hope this will help...
> > > --Dharmendra> To: ids@iiug.org
> > > > From: jfrancisco.navarro@gmail.com
> > > > Subject: sql syntax o update error [29460]
> > > > Date: Sat, 2 Feb 2013 10:07:59 -0500
> > > >
> > > > Hello:
> > > >
> > > > I'm trying to execute this query to update one table from another,
> but
> > > ids
> > > > gime me a -201 error. I dont't know why, can anyone help me?
> > > >
> > > > update pruebas set a.tubo=b.tubo FROM pruebas a, pruebastor b where> > > > b.tubo<>'' and a.codigo=b.codigo and a.baja='3000-01-01 00:00:00' and
> > > > b.baja='3000-01-01 00:00:00' and a.clase='1' and a.tubo=''
> > > >
> > > > Regards.
> > > >
> > > > --0023544713e09ad79b04d4bf3926
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > >
> > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --20cf302ef940be68a604d4cf95f7
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --047d7b6d93407587ac04d4e7095e
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--047d7b67789433830b04d4e71a1d