Hi, Tim

If I delete all records from the table and would like to start from 0 again for the identifier field. How to do that.

BTW, How can I get the last id number for identifier field?


Thanks

Jianfei

Jianfei,

<< If I delete all records from the table and would like to start from 0
again for the identifier field. How to do that. >>

Just use an ALTER TABLE statement that does the following:

ALTER TABLE MyTable
DROP COLUMN IDColumn,
ADD COLUMN IDColumn INTEGER GENERATED ALWAYS AS IDENTITY....

http://www.elevatesoft.com/edb1sql_alter_table.htm

As of 1.06, you'll be able to use the ALTER COLUMN IDColumn AS syntax
instead.

<< BTW, How can I get the last id number for identifier field? >>

Just use a:

SELECT MAX(IDColumn) FROM MyTable

query.

--
Tim Young
Elevate Software
www.elevatesoft.com

> ALTER TABLE MyTable
> DROP COLUMN IDColumn,
> ADD COLUMN IDColumn INTEGER GENERATED ALWAYS AS IDENTITY....

Does not work. I guess that info must be stored somewhere.

Jianfei

Jianfei,

<< Does not work. I guess that info must be stored somewhere. >>

What was the exact SQL statement that you used ?  You need to make sure to
set the seed value.

--
Tim Young
Elevate Software
www.elevatesoft.com

   SQLc := 'ALTER TABLE '+TN+
           ' DROP COLUMN "xID",'+
           ' ADD COLUMN "xID" INTEGER GENERATED BY DEFAULT AS IDENTITY
(START WITH 0, INCREMENT BY 1)';

Jianfei

> << BTW, How can I get the last id number for identifier field? >>
>
> Just use a:
>
> SELECT MAX(IDColumn) FROM MyTable
>
> query.

If some records get deleted, then, the MAX(IDColumn) will not be able tell
the last ID number!

Jianfei

Jianfei,

<< If some records get deleted, then, the MAX(IDColumn) will not be able
tell the last ID number! >>

Perhaps you could tell me what you need the last number for, and then I can
tell you how best to do what you want.

--
Tim Young
Elevate Software
www.elevatesoft.com

Jianfei,

<<    SQLc := 'ALTER TABLE '+TN+
           ' DROP COLUMN "xID",'+
           ' ADD COLUMN "xID" INTEGER GENERATED BY DEFAULT AS IDENTITY
(START WITH 0, INCREMENT BY 1)'; >>

Okay, I'm going to need to make some modifications to accomodate this in
1.06 because currently the see value is not reset as expected.

--
Tim Young
Elevate Software
www.elevatesoft.com