RE: How to copy a row

>From: Alain.Leroy@csl.sni.be (Alain Leroy) >Newsgroups: comp.databases.informix >Subject: [News] How to copy a row ? > > As the subject says, I want to duplicate a row in a table. To >understand me, read the following SQL statement and tell me what you >think about it : > > insert into lang(id, descr) select 9, descr from lang where id=4;> > So I choose the row with Id=4 and copy it into the same table >with a new Id (9). > > Well, It DOESN'T WORK !!!!! :( Yes. You will get an error code -360 which reads, "Cannot modify table or view used in a subquery. This UPDATE or INSERT statement uses data taken from the same table in a subquery. This is not allowed because of the danger of getting into an endless loop. First insert the data into a temporary table; then refer to the temporary table in the UPDATE or INSERT command." (Taken from the INFORMIX ERROR MESSAGES MANUAL). From reading the above, INFORMIX could see the possible usage but didn't want to allow its use. At one time, it was probably allowed then turned off in later versions of the INFORMIX software. > > Can someone tell me how to do that damned copy (in a single >request if possible and without using temp tables) ? > Yes. You can do it if you create a procedure (ie., a trigger process) that will insert a new row into the table with the value of 9 instead of 4 whenever a row is updated in which the old and new value of the field "id" is 4. Of course, if you had prior statements which qualified when to perform this insert, you will have to add that to the procedure. Of course, using a temp table such as below would probably be a lot easier: create temp table ytable (id smallint, descr char(20)); #size of descr guessed insert into ytable select 9, descr from lang where id = 4; insert into lang select * from ytable;........drop the temp table or let the isql (or dbaccess) drop it when you exit out. I know this doesn't help ya much, but it may be an easier approach to using a temp file than what you may have been thinking. -- ---------------------------------------------------------------------------- Clifton M. Bean Live and in Person in Saint Petersburg, Russia Grand Hotel Europe, #444 Employed by KPMG Peat Marwick (PEG), Wash, D.C. Saint Petersburg, Russia ------------------------------------------------ Voice: +7-812-119-6000 Snail Mail: P.O. Box 53 Fax: +7-812-119-6001/6003 SF-535 01 Lappeenranta, Finland ---------------------------------------------------------------------------- Current: clifton@clifbean.spb.su After Sept 30: clifton@egsner.cirr.com ----------------------------------------------------------------------------