Re: Copying a record using SQL and updating the key

"Of course, once you've gone that far, might as well use an RPG program, eh? <grin>"

Yep. Just about. I was hoping I could just do it with SQL alone but it looks like I can't, directly.

The temporary table idea might work though. I'll see where that takes me.

It would be great to be able to do a select like:

Select *, except key from mytable

So that you could omit a field or fields from the select instead of having to name each field.

Then you could do something like

Insert (select 'value', *, except key from myfile) into myfile. That would be cool.

Thanks,

Pete


Joe Pluta wrote:

Pete Helgren wrote:

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.

Not pretty, but there are two SQL options that I can think off:

1. Insert the record into a temporary duplicate table, change the key (or any other fields), then insert that record back into the original. It works, allows SELECT * in both cases AFAIK.

2. Use embedded SQL. SELECT * into an EDS (externally described data structure), update the EDS, INSERT from the DS.

Of course, once you've gone that far, might as well use an RPG program, eh? <grin>

Joe