Reset Serial Number
A user emptied a test table and wanted the SERIAL counter reset so the next insert gets 1. One reply claimed dropping and recreating the table was the only option, but others gave simpler fixes: ALTER TABLE ... MODIFY col INT then back to SERIAL, or in one step ALTER TABLE ... MODIFY (col SERIAL(1)). Jonathan Leffler noted no table drop is needed at all — insert a row with the serial value 2147483647 and delete it, after which the next generated value is 1 (very old servers needed 2147483646 then 0).
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello, I want to reset a serial number so that the next row of entered data is number 1. I have been experimenting with this table, prior to it going live. It had reached number 25. I have now deleted all data. What's the "best" way of doing this? Regards, Malcolm.
On Tue, 16 Sep 2003 07:12:58 -0400 (EDT), Malcolm Garbett wrote: >Hello, > >I want to reset a serial number so that the next row of entered data is number 1. > >I have been experimenting with this table, prior to it going live. It had reached number 25. I have now deleted all data. > >What's the "best" way of doing this? > > Drop and recreate it, no other option. -- Malc_p -- XS2Mail: Check your mail anywhere http://www.xs2mail.com/
{ first remove serial column by making it an integer column }
alter table tableX modify (colX int);
{ then turn it back into a serial column. this will reset number back to 1
on an empty
table or n+1 on a table with data in it }
alter table tableX modify (colX serial);
-----Original Message-----
From: Malc_p [mailto:malc_p@btinternet.com]
Sent: Tuesday, September 16, 2003 8:08 AM
To: ids@iiug.org
Subject: Re: Reset Serial Number [1882]
On Tue, 16 Sep 2003 07:12:58 -0400 (EDT), Malcolm Garbett wrote:
>Hello,
>
>I want to reset a serial number so that the next row of entered data is
number 1.
>
>I have been experimenting with this table, prior to it going live. It had
reached number 25. I have now deleted all data.
>
>What's the "best" way of doing this?
>
>
Drop and recreate it, no other option.
--
Malc_p
--
XS2Mail: Check your mail anywhere
http://www.xs2mail.com/
Please do not transmit orders or instructions regarding a UBS account by
email. The information provided in this email or any attachments is not an
official transaction confirmation or account statement. For your protection,
do not include account numbers, Social Security numbers, credit card
numbers, passwords or other non-public information in your email. Because
the information contained in this message may be privileged, confidential,
proprietary or otherwise protected from disclosure, please notify us
immediately by replying to this message and deleting it from your computer
if you have received this communication in error. Thank you.
UBS Financial Services Inc.
UBS International Inc.
Malcolm Garbett wrote: > Hello, > > I want to reset a serial number so that the next row of entered data is number 1. > > I have been experimenting with this table, prior to it going live. It had reached number 25. I have now deleted all data. > > What's the "best" way of doing this? Mmm, the problem is that the next serial value is stored internally. You will have to drop the column and re-create it in 2 different transactions. Otherwise, it may be easier to just drop the entire table and re-create it, as there is no data in it anyway. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+
Malcolm,
{ first remove serial column by making it an integer column }
alter table tableX modify (colX int);
{ then turn it back into a serial column. this will reset number back to 1
on an empty
table or n+1 on a table with data in it }
alter table tableX modify (colX serial);
Wayne
-----Original Message-----
From: Malcolm Garbett [mailto:malcolm.g@newall.co.uk]
Sent: Tuesday, September 16, 2003 7:13 AM
To: ids@iiug.org
Subject: Reset Serial Number [1880]
Hello,
I want to reset a serial number so that the next row of entered data is
number 1.
I have been experimenting with this table, prior to it going live. It had
reached number 25. I have now deleted all data.
What's the "best" way of doing this?
Regards,
Malcolm.
Please do not transmit orders or instructions regarding a UBS account by
email. The information provided in this email or any attachments is not an
official transaction confirmation or account statement. For your protection,
do not include account numbers, Social Security numbers, credit card
numbers, passwords or other non-public information in your email. Because
the information contained in this message may be privileged, confidential,
proprietary or otherwise protected from disclosure, please notify us
immediately by replying to this message and deleting it from your computer
if you have received this communication in error. Thank you.
UBS Financial Services Inc.
UBS International Inc.
To change serial value to a larger value (than current):
ALTER TABLE tablenameMODIFY ser_col_name SERIAL(new_number);
To change to a lesser value:
alter table tablenamemodify ser_col_name serial (2147483647);
Insert into tablename (serial_col) value (0);
ALTER TABLE
MODIFY (ser_col_name SERIAL(new_number));
-----Original Message-----
From: Zablatzky, .... [mailto:Wayne.Zablatzky@ubs.com]
Sent: Tuesday, September 16, 2003 8:54 AM
To: ids@iiug.org
Subject: RE: Reset Serial Number [1887]
Malcolm,
{ first remove serial column by making it an integer column }
alter table tableX modify (colX int);
{ then turn it back into a serial column. this will reset number back
to 1
on an empty
table or n+1 on a table with data in it }
alter table tableX modify (colX serial);
Wayne
-----Original Message-----
From: Malcolm Garbett [mailto:malcolm.g@newall.co.uk]
Sent: Tuesday, September 16, 2003 7:13 AM
To: ids@iiug.org
Subject: Reset Serial Number [1880]
Hello,
I want to reset a serial number so that the next row of entered data is
number 1.
I have been experimenting with this table, prior to it going live. It
had
reached number 25. I have now deleted all data.
What's the "best" way of doing this?
Regards,
Malcolm.
Please do not transmit orders or instructions regarding a UBS account by
email. The information provided in this email or any attachments is not
an
official transaction confirmation or account statement. For your
protection,
do not include account numbers, Social Security numbers, credit card
numbers, passwords or other non-public information in your email.
Because
the information contained in this message may be privileged,
confidential,
proprietary or otherwise protected from disclosure, please notify us
immediately by replying to this message and deleting it from your
computer
if you have received this communication in error. Thank you.
UBS Financial Services Inc.
UBS International Inc.
At 15:53 16/09/03, Zablatzky, .... wrote:
>Malcolm,
>
>{ first remove serial column by making it an integer column }
>alter table tableX modify (colX int);
alter table tableX modify ( colX serial (1) )does the biz in one hit
HTH
--
MarkT
=========================
E M Thornber CEng MIEE
Enchanted Systems Limited
Software Toolsmiths
+44 (0) 1503 272097
Whoa! Timeout!
Nonsense - there is no need to drop the table.
INSERT INTO Table(SerialCol, ...) VALUES(2147483647, ...);
DELETE FROM Table WHERE SerialCol = 2147483647;
The next value generated will be 1.
In older versions of the server - I forget exactly how old, but let's guess
early 5.0x and before - you had to insert 2147483646 and then 0 as
otherwise, the serial generator got stuck. I've not seen that as a problem
for a number of years now. (Digs in email archive - found references in
1991, 1993 on the subject; pretty old; my email in 1991 explicitly
mentioned SE 4.00.UD2).
--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Data Management
4100 Bohannon Drive, Menlo Park, CA 94025
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"
|---------+---------------------------->
| | "Malc_p " |
| | <malc_p@btinterne|
| | t.com> |
| | Sent by: |
| | forum.subscriber@|
| | iiug.org |
| | |
| | |
| | 09/16/2003 05:08 |
| | AM |
|---------+---------------------------->
>-------------------------------------------------------------------------------
--------------------------------------------------------------|
| |
| To: ids@iiug.org |
| cc: |
| Subject: Re: Reset Serial Number [1882] |
>-------------------------------------------------------------------------------
--------------------------------------------------------------|
On Tue, 16 Sep 2003 07:12:58 -0400 (EDT), Malcolm Garbett wrote:
>Hello,
>
>I want to reset a serial number so that the next row of entered data is
number 1.
>
>I have been experimenting with this table, prior to it going live. It had
reached number 25. I have now deleted all data.
>
>What's the "best" way of doing this?
>
>
Drop and recreate it, no other option.
--
Malc_p
--
XS2Mail: Check your mail anywhere
http://www.xs2mail.com/