Re: Changing serial value

>At 10:14 AM 8/19/97 +0200, marko kopac (marko.kopac@mais.si) wrote: >>I don't know how to change a serial value in a table (Informix >>database). I need this to copy a record to the same table. First I copy >>a record to a temp table. There I try to change the serial value and >>then to insert to the original table. This is my idea. Has anybody some >>another idea hove to solve this problem? >Date: Tue, 19 Aug 1997 06:47:57 -0700 >From: Dave Otto <dotto@themoneystore.com> >X-Informix-List-Id: <list.16024> > >If you want to be able to control the value inserted, you need to >use a different data type. If you are just trying to clone a >record, exclude the "serial" column from the insert. You cannot >specify the value of a serial column. I think Dave's answer is slightly misleading, so I'm going to try to clarify it. First of all, you cannot update the value of a serial column: -232: A SERIAL column(s) may not be updated. Marko was trying to copy a record to the same table by copying a record from the table into a temp table, and then re-inserting the record into the main table. This can be done provided that the insertion back into the main table specifies 0 for the serial column value: SELECT * FROM TableA WHERE PKey = pkvalue INTO TEMP T1; INSERT INTO TableA SELECT 0, Column2, Column3, ... FROM T1; DROP TABLE T1; Dave says you cannot specify the value of a serial column. This isn't entirely accurate. You can specify a serial value on INSERT: CREATE TEMP TABLE T (S SERIAL(1000000) NOT NULL PRIMARY KEY); INSERT INTO T VALUES(1); -- OK, inserts row 1 INSERT INTO T VALUES(0); -- OK, inserts row 1000000 INSERT INTO T VALUES(1000000); -- Fails, dup entry in primary key INSERT INTO T VALUES(1000001); -- OK, inserts row 1000001 INSERT INTO T VALUES(2000000); -- OK, inserts row 2000000 INSERT INTO T VALUES(0); -- OK, inserts row 2000001 INSERT INTO T VALUES(2); -- OK, inserts row 2 You can also insert negative numbers. If you insert 2**31-2, then 0, the serial numbers recycle starting at 1 on the next insert (but don't try 2**31-1; on many older versions of the engines, it won't work properly). Note that a SERIAL column needs a unique index on it to enforce the uniqueness constraint -- that's what the primary key declaration does. If you omit it, then the column allows duplicates. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> PS: Warning I do not reply to messages with anti-spam in the return path.