MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » October 2012

Re: reset auto increment field.



fixed

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





Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact