Update with LOAD Statement

Hello, I must change a value of a field acording to the value of an other field in >8000 records. The relation wich fields must be changed with which value comes from an other system as an ascii file. So I generated with awk an sql script with 8000 update satements. ... UPDATE table set A=1 WHERE B='X'; UPDATE table set A=3 WHERE B='Z'; UPDATE table set A=8 WHERE B='G';.... I think it is better to create a script with a LOAD statement. How must I create the sql script whith LOAD statement, that LOADs this file (file.csv) like the 8000 UPDATEs obove? with file.csv 1;X 3;Z 3;G Greetings Ralf

Ralf Hackmann wrote: > Hello, > > I must change a value of a field acording to the value of an other > field in >8000 records. > The relation wich fields must be changed with which value comes from > an other > system as an ascii file. > So I generated with awk an sql script with 8000 update satements. > > ... > UPDATE table set A=1 WHERE B='X'; > UPDATE table set A=3 WHERE B='Z'; > UPDATE table set A=8 WHERE B='G';> .... > > I think it is better to create a script with a LOAD statement. > > How must I create the sql script whith LOAD statement, that LOADs this > file (file.csv) like the 8000 UPDATEs obove? > > with file.csv > 1;X > 3;Z > 3;G > > Greetings > > Ralf <BRAG> my SQSL tool (see sig) would let you achieve that pretty quickly with something along the lines of FOREACH INPUT FROM "yourfile" PATTERN DELIMITED INTO a, b; UPDATE table SET a=? WHERE b=? USING a, b;END FOREACH; or (faster) PREPARE s FROM UPDATE table SET a=? WHERE b=?; FOREACH INPUT FROM "yourfile" PATTERN DELIMITED INTO a, b; EXECUTE s USING a, b; DONE; or even (which would probably be the fastest) CREATE PROCEDURE u(a int, b char(1)); UPDATE tables SET a=a WHERE b=b;END PROCEDURE; INPUT FROM "yourfile" PATTERN DELIMITED EXECUTE PROCEDURE u(?, ?); </BRAG> -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

CREATE TABLE staging_table ( a int, b char(1)); LOAD FROM file.csv DELIMITER ";" INSERT INTO staging_table; But the UPDATE is difficult. You would be better off using a stored procedure or Marco's tool, though the 8000 SQLs would probably run just as fast, especially if you broke them up into multiple scripts running in parallel. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf 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, Jan 12, 2010 at 5:04 AM, Ralf Hackmann <ralf.hackmann@gmail.com>wrote: > Hello, > > I must change a value of a field acording to the value of an other > field in >8000 records. > The relation wich fields must be changed with which value comes from > an other > system as an ascii file. > So I generated with awk an sql script with 8000 update satements. > > ... > UPDATE table set A=1 WHERE B='X'; > UPDATE table set A=3 WHERE B='Z'; > UPDATE table set A=8 WHERE B='G';> .... > > I think it is better to create a script with a LOAD statement. > > How must I create the sql script whith LOAD statement, that LOADs this > file (file.csv) like the 8000 UPDATEs obove? > > with file.csv > 1;X > 3;Z > 3;G > > Greetings > > Ralf > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >