Update fields of a table from a list
User on Informix SE 7.25 wanted to bulk-update several columns of a table from a semicolon-delimited file keyed on a primary key, and thought a stored procedure loop was needed. Advice: load the file into a temp table (or an external table on 11.50+) and do a single correlated UPDATE ... SET (col1,col2) = (SELECT ...). His attempts gave error -201 syntax error; the cause was twofold: the subquery needed correlation to the outer table (jpv.artikel = fyar1sta.artikel, dropping the redundant self-join) and, as Jacques Renaut noted, a multi-column SET with a subquery requires an extra set of parentheses: SET (a,b) = ((SELECT ...)). With both fixes the update worked.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hallo,
I have 2 jobs to update Tables where the user give the data in an input text or csv list.
1) Input List with one row, one field must be change, where fields are in this row.
2) Input List has more than 2 rows, one field is primery index, I must update the other for every record of the list.
To 1)
This job I solved for my own with an temp table
with a.txt
1234
4578
CREATE temp table artikel (art CHAR(21)) with no log;
LOAD FROM "a.txt" INSERT INTO artikel (art);
UPDATE fyar2sta
SET bestverf = 64
WHERE artikel IN ( SELECT * FROM artikel );
to 2)
Here I don´t Know how to solve it
b.txt
1234;123;234;1
5678;456;789;3
The first row is primery key an I must update rows 2 to 4
I think I must import b.txt in a temp table with 4 rows and then update every record with a loop in a procedure.
But I don´t know How to code this.
Can anybody help me?
Greetings
Ralf
The Database is Informix SE 7.25
OK, I have no idea what you want to do with the b.txt input records. How about an example of what the resulting updates SHOULD look like. I do, however, agree that you will need a stored procedure or an external executable (written in some host languare like ESQL/C, 4GL, Perl-DBI, etc.) to accomplish this. You can't do it in pure SQL. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) 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, Feb 13, 2013 at 4:16 AM, Ralf Hackmann <ralf.hackmann@gmail.com>wrote: > The Database is Informix SE 7.25 > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >
On Wed, Feb 13, 2013 at 1:07 AM, Ralf Hackmann <ralf.hackmann@gmail.com>wrote:
> I have 2 jobs to update Tables where the user give the data in an input
> text or csv list.
>
> 1) Input List with one row, one field must be change, where fields are in
> this row.
>
> 2) Input List has more than 2 rows, one field is primary index, I must
> update the other for every record of the list.
>
> To 1)
> This job I solved for my own with an temp table
>
> with a.txt
> 1234
> 4578
>
> CREATE temp table artikel (art CHAR(21)) with no log;>
> LOAD FROM "a.txt" INSERT INTO artikel (art);>
> UPDATE fyar2sta
> SET bestverf = 64
> WHERE artikel IN ( SELECT * FROM artikel );
>
So, for this edit, the file contains the list of the primary keys for the
rows that must be updated, but the file does not contain any information
about the nature of the update itself? What you've got works, so this is
not critical.
> to 2)
> Here I don´t Know how to solve it
>
> b.txt
> 1234;123;234;1
> 5678;456;789;3
>
> The first row is primary key and I must update rows 2 to 4
>
The first row? Or the first field in each row is the primary key? You
will need to expand upon the updates you would perform based on that data —
because it is not at all clear to me what your requirement is. And without
good requirements , you'll only get mediocre answers.
I think I must import b.txt in a temp table with 4 rows and then update
> every record with a loop in a procedure.
>
Maybe, but it is not obvious how you get the 4 rows out of the two shown.
I assume that the semi-colon is a field separator?
--
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."
Sorry, I confused row with columns.
So I have 4 columns in a.txt
If I would manualy update a in the Database I make this a.sql
UPDATE table Set b=123, c=234, d=1 WHERE a=1234;
UPDATE table Set b=456, c=789, d=3 WHERE a=5678;
But in practice a.txt ha more than 1000 rows.
Is there a way to do this in a stored procedure, if I import a.txt in a temp table with 4 columns and make the Update above in a loop over each row of the temp table?
I dont know how to code this.
If you have 11.50 or later, then this is easy.
- Define an external table for the a.txt file:
- create external table a_ext( a int, b int, c int, d int ) using
(datafiles('disk:a.txt'), format 'delimited', delimiter ';');
- Join 'table' to the external table for the update:
- update table set (b, c, d) = (select b, c, d from a_ext where
a_ext.a = table.a) where table.a in (select a from a_ext);
- Clean up:
- drop table a_ext;
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
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 Thu, Feb 14, 2013 at 7:14 AM, Ralf Hackmann <ralf.hackmann@gmail.com>wrote:
> Sorry, I confused row with columns.
> So I have 4 columns in a.txt
>
> If I would manualy update a in the Database I make this a.sql
> UPDATE table Set b=123, c=234, d=1 WHERE a=1234;
> UPDATE table Set b=456, c=789, d=3 WHERE a=5678;>
> But in practice a.txt ha more than 1000 rows.
>
> Is there a way to do this in a stored procedure, if I import a.txt in a
> temp table with 4 columns and make the Update above in a loop over each row
> of the temp table?
>
> I dont know how to code this.
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
Am Donnerstag, 14. Februar 2013 14:03:51 UTC+1 schrieb Art S. Kagel:
> Join 'table' to the external table for the update:update table set (b, c, d) = (select b, c, d from a_ext where a_ext.a = table.a) where table.a in (select a from a_ext);Clean up:
In my SE 7.25 I tied this to import an it works
CREATE temp table jpv (
artikel CHAR(21),
artwgr SMALLINT,
vorzugl1 smallint,wbzkrit integer
) with no log;
LOAD FROM "b.txt" DELIMITER ";" INSERT INTO jpv (
artikel,
artwgr,
vorzugl1,wbzkrit);
SELECT COUNT * FROM JPV;
=>
artikel artwgr vorzugl1 wbzkrit
00005746-0200 303 509 5
00006019-0200 303 509 5
00006835-0200 303 509 5
00007610-0200 303 509 5
00015431-0200 303 509 5
In next Step I should use the query from Art S. Kagel below and I dont need a Stored Procedure
Thanks
> Join 'table' to the external table for the update:update table set (b, c, d) = (select b, c, d from a_ext where a_ext.a = table.a) where table.a in (select a from a_ext);Clean up:
Hello,
if I code in production system the proposal from S.Kagel I get an error message
Here my code:
CREATE temp table tmp_jpv (
artikel CHAR(21),
artwgr SMALLINT,
vorzugl1 smallint,wbzkrit integer
) with no log;
LOAD FROM "b.txt" DELIMITER ";" INSERT INTO tmp_jpv (
artikel,
artwgr,
vorzugl1,wbzkrit);
UPDATE fyar1sta
SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
FROM tmp_jpv jpv, fyar1sta ar1
WHERE jpv.artikel = ar1.artikel )
WHERE fyar1sta.artikel
IN ( SELECT artikel
FROM tmp_jpv );
( artwgr and teilfam should be eq artwgr and the other fields of b.txt should be updated in an other table)
If I run zhis I get the error message:
"201: A syntax error has occured
Error in line15Near character position 34"
Line 34: SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
Where is my mistake?
Or is this a problem whis the SE 7.25?
Greeting
Ralf
On Monday, February 18, 2013 8:47:48 AM UTC-6, Ralf Hackmann wrote:
> Hello,
>
> if I code in production system the proposal from S.Kagel I get an error message
>
>
>
> Here my code:
>
>
>
> CREATE temp table tmp_jpv (>
> artikel CHAR(21),
>
> artwgr SMALLINT,
>
> vorzugl1 smallint,
>
> wbzkrit integer
>
> ) with no log;
>
>
>
> LOAD FROM "b.txt" DELIMITER ";" INSERT INTO tmp_jpv (>
> artikel,
>
> artwgr,
>
> vorzugl1,
>
> wbzkrit);
>
>
>
> UPDATE fyar1sta
>
> SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
>
> FROM tmp_jpv jpv, fyar1sta ar1
>
> WHERE jpv.artikel = ar1.artikel )
>
> WHERE fyar1sta.artikel
>
> IN ( SELECT artikel
>
> FROM tmp_jpv );
>
>
>
> ( artwgr and teilfam should be eq artwgr and the other fields of b.txt should be updated in an other table)
>
>
>
> If I run zhis I get the error message:
>
>
>
> "201: A syntax error has occured
>
> Error in line15>
> Near character position 34"
>
> Line 34: SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
>
>
>
> Where is my mistake?
>
>
>
> Or is this a problem whis the SE 7.25?
>
>
>
> Greeting
>
>
>
> Ralf
I believe when you are updating multiple columns in your update statement, and you are using a query for the values you need another set of ()'s...so try this as your statement:
UPDATE fyar1sta
SET (artwgr, teilfam ) =( ( SELECT jpv.artwgr, jpv.artwgr
FROM tmp_jpv jpv, fyar1sta ar1
WHERE jpv.artikel = ar1.artikel ) )
WHERE fyar1sta.artikel
IN ( SELECT artikel
FROM tmp_jpv );
Jacques Renaut
IBM Informix Advanced Support
APD Team
The select statement in the right side of the SET clause has to return
exactly one row and of course you want it to relate to the row being
updated, so you are missing the correlation filter for that. Try:
UPDATE fyar1sta
SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
FROM tmp_jpv jpv, fyar1sta ar1
WHERE jpv.artikel = ar1.artikel
AND jpv.artikel = fyar1sta.artikel ) --
This filter was missing!
WHERE fyar1sta.artikel
IN ( SELECT artikel
FROM tmp_jpv );
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
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 Mon, Feb 18, 2013 at 9:47 AM, Ralf Hackmann <ralf.hackmann@gmail.com>wrote:
> Hello,
> if I code in production system the proposal from S.Kagel I get an error
> message
>
> Here my code:
>
> CREATE temp table tmp_jpv (
> artikel CHAR(21),
> artwgr SMALLINT,
> vorzugl1 smallint,> wbzkrit integer
> ) with no log;
>
> LOAD FROM "b.txt" DELIMITER ";" INSERT INTO tmp_jpv (
> artikel,
> artwgr,
> vorzugl1,> wbzkrit);
>
> UPDATE fyar1sta
> SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
> FROM tmp_jpv jpv, fyar1sta ar1
> WHERE jpv.artikel = ar1.artikel )
> WHERE fyar1sta.artikel
> IN ( SELECT artikel
> FROM tmp_jpv );
>
> ( artwgr and teilfam should be eq artwgr and the other fields of b.txt
> should be updated in an other table)
>
> If I run zhis I get the error message:
>
> "201: A syntax error has occured
> Error in line15> Near character position 34"
> Line 34: SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
>
> Where is my mistake?
>
> Or is this a problem whis the SE 7.25?
>
> Greeting
>
> Ralf
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
Am Montag, 18. Februar 2013 18:08:57 UTC+1 schrieb Art S. Kagel: > > WHERE jpv.artikel = ar1.artikel > AND jpv.artikel = fyar1sta.artikel ) -- This filter was missing! Isn´t this the same filter as the filter in the row before? Greetings Ralf
No. The filter in the line above (jpv.artikel = ar1.artikel) is joining the temp table to the copy of fyar1sta in the sub-query (which BTW is completely unnecessary) which is why you are returning multiple rows. You need to FILTER the rows from the temp table using the key of the current row in the outer UPDATE. Actually the following will be a bit faster by dropping the join in the SET clause subquery. Didn't notice before that you aren't using any data from the joined copy of fyar1sta: UPDATE fyar1sta SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr FROM tmp_jpv jpv WHERE jpv.artikel = fyar1sta.artikel ) WHERE fyar1sta.artikel IN ( SELECT artikel FROM tmp_jpv ); Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) 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 Tue, Feb 19, 2013 at 2:36 AM, Ralf Hackmann <ralf.hackmann@gmail.com>wrote: > Am Montag, 18. Februar 2013 18:08:57 UTC+1 schrieb Art S. Kagel: > > > > > WHERE jpv.artikel = ar1.artikel > > AND jpv.artikel = fyar1sta.artikel ) > -- This filter was missing! > > Isn´t this the same filter as the filter in the row before? > > Greetings > > Ralf > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >
Sorry Art, but when I run Your last quere I get the same error message
hiere my query from the production system:
CREATE temp table tmp_jpv (
artikel CHAR(21),
artwgr SMALLINT,
vorzugl1 smallint,wbzkrit integer
) with no log;
LOAD FROM "b.txt" DELIMITER ";" INSERT INTO tmp_jpv (
artikel,
artwgr,
vorzugl1,wbzkrit);
UPDATE fyar1sta
SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
FROM tmp_jpv jpv
WHERE jpv.artikel = fyar1sta.artikel )
WHERE fyar1sta.artikel
IN ( SELECT artikel
FROM tmp_jpv );
A syntax error has occured
Error in Line 15
Near caracter position 34
Line15:
SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
Ralf
On Wednesday, February 20, 2013 8:47:03 AM UTC-6, Ralf Hackmann wrote:
> Sorry Art, but when I run Your last quere I get the same error message
>
>
>
> hiere my query from the production system:
>
>
>
> CREATE temp table tmp_jpv (>
> artikel CHAR(21),
>
> artwgr SMALLINT,
>
> vorzugl1 smallint,
>
> wbzkrit integer
>
> ) with no log;
>
>
>
> LOAD FROM "b.txt" DELIMITER ";" INSERT INTO tmp_jpv (>
> artikel,
>
> artwgr,
>
> vorzugl1,
>
> wbzkrit);
>
>
>
> UPDATE fyar1sta
>
> SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
>
> FROM tmp_jpv jpv
>
> WHERE jpv.artikel = fyar1sta.artikel )
>
> WHERE fyar1sta.artikel
>
> IN ( SELECT artikel
>
> FROM tmp_jpv );
>
>
>
> A syntax error has occured
>
> Error in Line 15
>
> Near caracter position 34
>
>
>
> Line15:
>
> SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
>
>
>
> Ralf
So once again I'll say, add an extra set of ()'s around the right side of your set piece of the update statement (according to the SQL syntax guide if you have multiple columns to update it wants an extra set of ()'s around the select statement expression)...like this:
set (artwgr, teilfam ) = ( ( select .... blah blah ) )
where etc...
Jacques Renaut
IBM Informix Advanced Support
APD Team
Ralf:
Jacques is correct, my fault, you need another layer of parenthesis around
the SELECT on the right side of the SET clause. One set for the
multi-column SET and another for the sub-query itself.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
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, Feb 20, 2013 at 10:21 AM, jrenaut <jprenaut@yahoo.com> wrote:
> On Wednesday, February 20, 2013 8:47:03 AM UTC-6, Ralf Hackmann wrote:
> > Sorry Art, but when I run Your last quere I get the same error message
> >
> >
> >
> > hiere my query from the production system:
> >
> >
> >
> > CREATE temp table tmp_jpv (> >
> > artikel CHAR(21),
> >
> > artwgr SMALLINT,
> >
> > vorzugl1 smallint,
> >
> > wbzkrit integer
> >
> > ) with no log;
> >
> >
> >
> > LOAD FROM "b.txt" DELIMITER ";" INSERT INTO tmp_jpv (> >
> > artikel,
> >
> > artwgr,
> >
> > vorzugl1,
> >
> > wbzkrit);
> >
> >
> >
> > UPDATE fyar1sta
> >
> > SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
> >
> > FROM tmp_jpv jpv
> >
> > WHERE jpv.artikel = fyar1sta.artikel )
> >
> > WHERE fyar1sta.artikel
> >
> > IN ( SELECT artikel
> >
> > FROM tmp_jpv );
> >
> >
> >
> > A syntax error has occured
> >
> > Error in Line 15
> >
> > Near caracter position 34
> >
> >
> >
> > Line15:
> >
> > SET (artwgr, teilfam ) = ( SELECT jpv.artwgr, jpv.artwgr
> >
> >
> >
> > Ralf
>
> So once again I'll say, add an extra set of ()'s around the right side of
> your set piece of the update statement (according to the SQL syntax guide
> if you have multiple columns to update it wants an extra set of ()'s around
> the select statement expression)...like this:
>
> set (artwgr, teilfam ) = ( ( select .... blah blah ) )
> where etc...
>
> Jacques Renaut
> IBM Informix Advanced Support
> APD Team
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
Am Mittwoch, 20. Februar 2013 17:34:33 UTC+1 schrieb Art S. Kagel:
> Ralf:
>
> Jacques is correct, my fault, you need another layer of parenthesis around the SELECT on the right side of the SET clause. One set for the multi-column SET and another for the sub-query itself.
Jacques an Art, thank You very much, now it works
a.txt:
00005746-0200;301;509;5
00006019-0200;302;509;5
00006835-0200;303;509;5
00007610-0200;304;509;5
00015431-0200;305;509;5
a.sql:
CREATE temp table tmp_jpv (
artikel CHAR(21),
artwgr SMALLINT,
vorzugl1 smallint,wbzkrit integer
) with no log;
LOAD FROM "b.txt" DELIMITER ";" INSERT INTO tmp_jpv (
artikel,
artwgr,
vorzugl1,wbzkrit);
UPDATE fyar1sta
SET (artwgr, teilfam ) = (( SELECT jpv.artwgr, jpv.artwgr
FROM tmp_jpv jpv
WHERE jpv.artikel = fyar1sta.artikel ))
WHERE fyar1sta.artikel
IN ( SELECT artikel
FROM tmp_jpv );
SELECT artikel, artwgr, teilfam
FROM fyar1sta
WHERE artikel
IN ( SELECT artikel
FROM tmp_jpv );
Result:
artikel artwgr teilfam
00005746-0200 301 301
00006019-0200 302 302
00006835-0200 303 303
00007610-0200 304 304
00015431-0200 305 305