Rearrange Identity values

Question

Post reply

Rearrange Identity values

rose_red1947

SSC Journeyman

Points: 99
More actions
November 14, 2007 at 9:35 am

#91461

Hi
I have two fields, first field is with identity values like
1,A
2,B
3,C
4,D
5,E
6,F
7,G
8,H
9,I
10,J
I need to delete values 3,C and 7,G after this i want to rearrange identity values to get values like this
1,A
2,B
3,D
4,E
5,F
6,H
7,I
8,J
Can any one help

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

EvilPostIT SSCrazy Points: 2578 More actions · Answer 1

Drop the ID field and create it again.

Nuke the site from orbit, its the only way to be sure... :w00t:

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 2

rose_red1947 (11/14/2007)
Hi
I have two fields, first field is with identity values like
1,A
2,B
3,C
4,D
5,E
6,F
7,G
8,H
9,I
10,J
I need to delete values 3,C and 7,G after this i want to rearrange identity values to get values like this
1,A
2,B
3,D
4,E
5,F
6,H
7,I
8,J
Can any one help

This query will select the list and create a list of numbers, in order.

SELECT ROW_NUMBER() OVER (ORDER BY col2) as NewId

FROM MyTable

But if you're really using it against an identity field, you'll need to combine the INSERT statement with setting IDENTITY_INSERT on and you'll want to reseed the identity field to be the max value from the new set of data.

If you're really trying to maintain gap free data, you might be better off maintaining a third column with the listed order, rather than relying on the identity column to do that for you.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

I'm curious though... with over two billion numbers available to the INT datatype, why do you feel it's necessary to keep the ID column "gap free"? What is the business requirement behind that?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pdross2000 Old Hand Points: 363 More actions · Answer 4

you could try

delete from tablename where id in (3,7)

set identity_insert tablename on

declare @id int

set @id = 0

update table

set id = @id,

@id = @id+1

set identity_insert tablename off

Paul Ross

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

Maybe that works in 2k5... just not in 2k... yeup, I know... it's a 2k5 forum...

Server: Msg 8102, Level 16, State 1, Line 4

Cannot update identity column 'ID'.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pdross2000 Old Hand Points: 363 More actions · Answer 6

delete from tablename where id in (3,7)

select value

into #tmp

from tablename

delete from tablemane

dbcc checkident( tablename, reseed, 0)

insert into tablename

select value

from #tmp

order by value

should work

Paul Ross

Bob Fazio SSChampion Points: 10794 More actions · Answer 7

I'm with Jeff on this. Why do you need to do this? But more that that, have you verfied there are no FK's against this table?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

Jeff Moden (11/19/2007)
Maybe that works in 2k5... just not in 2k... yeup, I know... it's a 2k5 forum...
Server: Msg 8102, Level 16, State 1, Line 4
Cannot update identity column 'ID'.

You get that even with the IDENTITY_INSERT turned on? strange... You should be able to, even on SQL2000? Or was there something else in the syntax I missed?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

Bob Fazio (11/19/2007)
I'm with Jeff on this. Why do you need to do this? But more that that, have you verfied there are no FK's against this table?

Agreed - you might care to look up "update anomaly" and Normalization. If you don't pay attention, this is a pretty darn'ed efficient way to screw up your data integrity. Nothing like, say, changing the contents of a customer's order by changing what ItemID=1234 is for example..... And this would be EVERY item on EVERY order, more or less....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 10

Matt Miller (11/19/2007)
Jeff Moden (11/19/2007)
Maybe that works in 2k5... just not in 2k... yeup, I know... it's a 2k5 forum...
Server: Msg 8102, Level 16, State 1, Line 4
Cannot update identity column 'ID'.
You get that even with the IDENTITY_INSERT turned on? strange... You should be able to, even on SQL2000? Or was there something else in the syntax I missed?

Heh... It's IDENTITY_INSERT... not IDENTITY_UPDATE... 😛

Does anyone know if you can update an Identity column in 2k5 with SET IDENTITY_INSERT ON? I'd bet not but one never knows.... 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 11

Jeff Moden (11/19/2007)
Heh... It's IDENTITY_INSERT... not IDENTITY_UPDATE... 😛
Does anyone know if you can update an Identity column in 2k5 with SET IDENTITY_INSERT ON? I'd bet not but one never knows.... 😀

oops....I did miss that one....would have bit me too. I will need more coffee before visiting this thread again...:)

And now - you can't directly update an Identity field, even in 2005. However, since you have the ability to insert, you can "update" an identity value through the judicious use of INSERT and DELETE....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

Heh... yeah, coffee works for me, too. Don't feel bad... I tried the code and had to think about why it wasn't working 😛

Easiest way to do this is to copy the data to a temp table, truncate the orginal, copy the data back...

... but only if I had enough coffee 😉

I'd still like the OP to answer my original question, though...

Why is it necessary to maintain a gap-less IDENTITY on this table? In fact, why have an IDENTITY at all, in this case?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)