SQL Statement for Resetting Serial Column ?

Terron Wright

unread,

Jun 19, 1997, 3:00:00 AM6/19/97

to

Can someone give me a good example of how to use the alter table
statement for resetting a serial column next number.

Roger Tomas

unread,

Jun 19, 1997, 3:00:00 AM6/19/97

to

Terron Wright wrote:
>
> Can someone give me a good example of how to use the alter table
> statement for resetting a serial column next number.

I know that the documentation mentions that you can use ALTER TABLE
to reset a serial column, but I have yet to figure it out. Instead,
you should insert the maximum allowed value into the serial column
and force it to cycle back to 1. You can then delete the row if you
don't want it. We do this for populating databases with test data
so that we can get reproducible results (ie. get the same serial
values out of the database).

-----------
Roger Tomas
AG Communication Systems

Richard Thomas

unread,

Jun 19, 1997, 3:00:00 AM6/19/97

to

At 08:31 19/06/97 -0400, you wrote:
>Can someone give me a good example of how to use the alter table
>statement for resetting a serial column next number.
>

Example:

table is called 'tab'
column is called 'col'
required next number = 123

alter table tab modify col serial(123);

<flamebait>
Correct me if I'm wrong, everybody!!!
</flamebait>

Cheers,

Richard.

Ramdas Gopinath

unread,

Jun 19, 1997, 3:00:00 AM6/19/97

to

You cannot use the Alter table for resetting the Serial Column. You
can alter and add a Serial Column, but the table should not have any
Data at that time.
Gopi.

--------------------------------------------------
Ramdas Gopinath E-mail: rg...@rankin.com
602-997 6996 X125 Fax: 602-997 4256
--------------------------------------------------

Mark D. Stock

unread,

Jun 20, 1997, 3:00:00 AM6/20/97

to

Richard Thomas wrote:
>
> At 08:31 19/06/97 -0400, you wrote:
> >Can someone give me a good example of how to use the alter table
> >statement for resetting a serial column next number.
> >
>
> Example:
>
> table is called 'tab'
> column is called 'col'
> required next number = 123
>
> alter table tab modify col serial(123);
>
> <flamebait>
> Correct me if I'm wrong, everybody!!!
> </flamebait>

Except if the table has data in, and the MAX(col) is already higher
than the required next number.

In that case the ALTER TABLE works but does nothing!

Cheers,
--
Mark.

+------------------------------------------------------------------+
|Mark D. Stock - The West Solutions Group http://www.west.co.za |
| The Informix FAQ is at http://www.iiug.org |
|mailto:ma...@west.co.za +-----------------------------------------+
|Tel: +27 11 803 2151 |If it doesn't work, force it! |
|Fax: +27 11 803 2189 |If it breaks, it needed replacing anyway!|
|Cell: +27 83 250 2325 |Well, that's how I code anyway! |
+------------------------+-----------------------------------------+

L.Shaw

unread,

Jun 21, 1997, 3:00:00 AM6/21/97

to

I thought it was possible to amend the table with data to an integer
column, then change it back to serial with a specified starting integer
using dbaccess.

Might be wrong though its a long time since I thought I did it.

"Ramdas Gopinath" <rg...@rankin.com> wrote in article
<5oc0c3$m...@cssun.mathcs.emory.edu>...

Mark D. Stock

unread,

Jun 23, 1997, 3:00:00 AM6/23/97

to

You don't even need to do that. You CAN use ALTER TABLE to change
the next number.