Re: duplicating a record in SPL

You'd like to do: INSERT INTO Table1 SELECT 0, Col2, Col3 FROM Table WHERE Col1 = value; You can't refer to the target table of the INSERT in the SELECT statement, so life gets harder... SELECT * FROM Table WHERE Col1 = value INTO TEMP T1; INSERT INTO Table SELECT 0, Col2, Col3 FROM T1; Of course, this requires you to list all columns except the serial column as well as the serial column. If you have no mechanism for generating the whole list of columns, then you are probably snookered. You can't get around it by creating a permanent table (requires all columns to be listed), or a temporary table (requires all columns...), and you can't update a SERIAL, and ... You can find out the list of all columns in ESQL/C by using PREPARE and DESCRIBE on 'SELECT * FROM Table' -- the only method I know which tells you what the columns in a temporary table are. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> }From: tdenbow@mathworks.com (Toby Denbow) }Date: Mon, 25 Mar 1996 16:22:39 -0500 }X-Informix-List-Id: <news.22471> } }Say I have a table called tab1 with three columns in it, called col1, }col2, col3. } }col1 is a serial column that does not allow dups, }col2 is int, and col3 is int } }How can I duplicate a record so it gets teh next serial number for col1, }but simply duplicates the values for col2, and col3? The only column name }that I want to hardcode is the serial column. } }[...] } }insert into tab1 values (select * from tab1 where col1 = 100); }will not work because I would duplicate the serial number. } }select * from tab1 where col1 = 100 into temp temp_tab1; }alter table tab1 modify (col1 integer); }update temp_tab1 set col1 = 0 where col1 = 100; }insert into tab1 values (select * from temp_tab1); }will not work because you cannot do an alter table on a temp table. } }Any suggestions? }-Toby Denbow } }== Toby Denbow ========================= tdenbow@mathworks.com == } The MathWorks, Inc. info@mathworks.com } 24 Prime Park Way http://www.mathworks.com } Natick, MA 01760-1500 ftp.mathworks.com }==== Tel: 508-653-1415 === Fax: 508-650-6725 ==================== }