Re: How to copy a row

Clifton M. Bean (root@clifbean.spb.su) wrote: : >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. why not just use select descr from lang where id = 4 into temp a; insert into lang (id, descr) select 9, descr from a;