How do I copy records?
Kristi B, on Informix 7.24.uc5 with Baan IV, wanted to copy rows with first column 001 as new rows with 075 without typing every column, and Baan hash columns caused duplicate-value errors. Tony Flaherty said hash columns cannot be handled manually. Kristi reported an update approach worked. stumbde suggested unloading to a file, editing 001 to 075 with vi, and loading back.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I'm trying to copy all records where the first field is "001" back into the
table and changing that field to "075". So, I'll have a duplicate set of
records, except for the first field.
I'm running 7.24.uc5, Baan IV (mgmt decision).
Here are my steps:
select * from table
where column="001"
into temp temptable;
drop index on table
insert into table select "075", ?? from temptable
index table
(The ?? is where I get stuck. How do I insert the rest of the columns? Do I
have to type them all one by one? AND Baan uses hash columns, so the second
set of data won't insert back into table because sql says there are
duplicate values. What do I do with these hashes?)
help.
I have a friend who administers a BaaN system, I believe from conversations
with him that you cannot manually process these hash columns as they contain
BaaN compressed indexes?
As for the general question of the SQL the reply from mars1972 should work.
--
---------------------------------------
Tony Flaherty aef@mfs.misys.co.uk
Analyst Programmer
Misys Financial Systems
All statements and opinions are my own,
Misys don't pay me enough to have opinions
on their behalf
.
Kristi B wrote in message <8gh1vc$24na$1@razor.dct.com>...
>I'm trying to copy all records where the first field is "001" back into the
>table and changing that field to "075". So, I'll have a duplicate set of
>records, except for the first field.
>
>I'm running 7.24.uc5, Baan IV (mgmt decision).
>Here are my steps:
>
>select * from table
>where column="001"
>into temp temptable;>
>drop index on table>
>insert into table select "075", ?? from temptable>
>index table
>
>(The ?? is where I get stuck. How do I insert the rest of the columns? Do I
>have to type them all one by one? AND Baan uses hash columns, so the second
>set of data won't insert back into table because sql says there are
>duplicate values. What do I do with these hashes?)
>
>help.
>
>
>
Yes, the update does work. Thanks. As far as the indexing (hash columns) - that's just an ugly Baanism that I'll have to work through. Tony Flaherty wrote in message <8git8t$7jk$1@hermes.mfs.misys.co.uk>... >I have a friend who administers a BaaN system, I believe from conversations >with him that you cannot manually process these hash columns as they contain >BaaN compressed indexes? > >As for the general question of the SQL the reply from mars1972 should work. > >-- >--------------------------------------- >Tony Flaherty aef@mfs.misys.co.uk >Analyst Programmer >Misys Financial Systems >All statements and opinions are my own, >Misys don't pay me enough to have opinions >on their behalf
Kristi
You can unload the data in a flat file using
unload to table.asc
select * from table
where column="001"Open this file in vi editior
use %s/^001/075/
Save this file
Now load this file back using
load from table.asc
insert into table
Kristi B <kristi@kaytee.com> wrote in message
news:8gh1vc$24na$1@razor.dct.com...
> I'm trying to copy all records where the first field is "001" back into
the
> table and changing that field to "075". So, I'll have a duplicate set of
> records, except for the first field.
>
> I'm running 7.24.uc5, Baan IV (mgmt decision).
> Here are my steps:
>
> select * from table
> where column="001"
> into temp temptable;>
> drop index on table>
> insert into table select "075", ?? from temptable>
> index table
>
> (The ?? is where I get stuck. How do I insert the rest of the columns? Do
I
> have to type them all one by one? AND Baan uses hash columns, so the
second
> set of data won't insert back into table because sql says there are
> duplicate values. What do I do with these hashes?)
>
> help.
>
>
>