Re: reset auto increment field.

So, if you had a table created with:
CREATE TABLE ROB/IDTEST
(MYIDCOL INT NOT NULL AS IDENTITY
SomeChar char (25),
PRIMARY KEY (MYIDCOL))

and you inserted a few rows into it and now have
MYIDCOL SOMECHAR
1 a
2 b
3 c

And you run this:
delete from rob/idtest where somechar='b'

you want to know if you can run this:
with t1 as (
select rank() over(order by myidcol ASC) as newid,
myidcol
from rob/idtest
order by myidcol ASC)
Update rob/idtest
set myidcol=(select newid
from t1
where t1.myidcol=idtest.myidcol)

No. but there may be some alteration of that possible.


Rob Berendt