Re: ProblemS with LOAD statement in 4gl

On Fri, 18 Feb 2005 19:01:03 GMT, Michael Krzepkowski <mkrzepkowski@hotmail.com> wrote: >> >> Just in case anyone has a better idea on how to do this, I'm trying to >> copy certain records in our database and change 1 of the key values at >> the same time. >> >> To oversimplify the scenario, let's say store_id is part of the >> primary key for a whole bunch of tables. >> >> I want to add a whole set of records that matches store_id = 1, except >> it will have store_id = 2. >> >> There are enough tables that I'm planning to select all the tables >> with a store_id column, build up a SELECT statement replacing the >> actual store_id column with the new store_id (and any serial keys with >> zero), unload it and reload it. >> >> (I wanted to do an INSERT INTO .... SELECT FROM...., but you can't do >> that if you're selecting from the same table you're inserting into). > >Try using a temporary table: > >- read whatever you need into it. >- update fields >- copy/update into target > >You have full control over transctions and error checking plus >you will not bother the file system with your data. The problem with using a temporary table is that I'd have to dynamically create them. Can I prepare a CREATE TABLE statement? I guess so, since it is SQL, but it seems like a lot of work.

Cartlon Shew wrote: > On Fri, 18 Feb 2005 19:01:03 GMT, Michael Krzepkowski > <mkrzepkowski@hotmail.com> wrote: >>>Just in case anyone has a better idea on how to do this, I'm trying to >>>copy certain records in our database and change 1 of the key values at >>>the same time. >>> >>>To oversimplify the scenario, let's say store_id is part of the >>>primary key for a whole bunch of tables. >>> >>>I want to add a whole set of records that matches store_id = 1, except >>>it will have store_id = 2. >>> >>>There are enough tables that I'm planning to select all the tables >>>with a store_id column, build up a SELECT statement replacing the >>>actual store_id column with the new store_id (and any serial keys with >>>zero), unload it and reload it. >>> >>>(I wanted to do an INSERT INTO .... SELECT FROM...., but you can't do >>>that if you're selecting from the same table you're inserting into). >> >>Try using a temporary table: >> >>- read whatever you need into it. >>- update fields >>- copy/update into target >> >>You have full control over transctions and error checking plus >>you will not bother the file system with your data. > > The problem with using a temporary table is that I'd have to > dynamically create them. > > Can I prepare a CREATE TABLE statement? I guess so, since it is > SQL, but it seems like a lot of work. Of course, but why bother? SELECT key1, key2, value1 + 1 as value1, value2 || " No!" as value2 FROM SourceTable INTO TEMP TempTable {WITH NO LOG}; INSERT INTO SourceTable SELECT * FROM TempTable; DROP TABLE TempTable; The two computations represent the change you want...rereading, I suppose that should be: SELECT 2 as store_id, ... FROM SourceTable INTO TEMP TempTable; -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/