RE: Copying a record using SQL and updating the key

For a file with columns KEY1, DTA1, DTA2

INSERT INTO mytable
SELECT 'NewKeyValue', DTA1, DTA2
WHERE KEY1 = 'OrigKeyValue'

You will not be able to use * to select your fields.

Eric

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Pete Helgren
Sent: Tuesday, May 13, 2008 2:16 PM
To: Midrange Systems Technical Discussion
Subject: Copying a record using SQL and updating the key


Is there a way to copy an existing record using SQL except for the key?
There is something way back in the recesses of my mind that says yes,
but I haven't been able to come up with syntax that works.

A statement like "Insert into myfile (select * from myfile where key =
1) " should copy a record with key = 1 and insert it. But in a uniquely
keyed file, it would cause a duplicate key error. Can I create a new
record with a new key value without identifying all the existing
fields? If my first field is the key, having to do something like
"insert into myfile (select
'2',field2,field3,field4,field5,field6....field50 from myfile where key
= 1) would create a valid new record with a key of '2' but is a hassle
to code all the field references when there are a bunch of them (50 in
this example).

Any tricks here or do I need to include all the fields in the select
statement individually in order to create a "copy" and insert it with a
new key value.

Thanks

Pete Helgren