× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



"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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.