Puzzle - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Puzzle

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2004-07-18 : 20:07:01

I've got a table with a column of sequence numbers.

After deleting a few rows, I'd like to run an update query that will keep the rows in the original sequence, but resequence the column starting with 1.

Sample Data:
texta, 1
textb, 2
textc, 3
textd, 4
texte, 5

After deleting 2 rows:
textb, 2
textc, 3
texte, 5

After the resequencing update
textb, 1
textc, 2
texte, 3

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-18 : 21:11:41

Why don't you just select the remaining ID's into a temp table with an identity column, then update the id row to be whatever the corresponding identity column is in the temp table.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

SamC
White Water Yakist

3467 Posts

Posted - 2004-07-18 : 21:22:09

Could do that.

I was thinking there might be a temp tableless form of

declare @cnt int
set @cnt = 0
update MyTable
SET @cnt = MySequence = @cnt + 1
WHERE ....

But I run out of imagination trying to come up with a JOIN or ORDERBY to give the desired result.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-18 : 21:32:59

Create Table #tempTable (data nvarchar(10), seq int)
Insert into #tempTable Values('textb',2)
Insert into #tempTable Values('textc',3)
Insert into #tempTable Values('texte',5)

Select *, newSeq = (Select count(*)+1 From #tempTable Where seq<A.seq) From #tempTable as A

Drop Table #tempTable

Corey

SamC
White Water Yakist

3467 Posts

Posted - 2004-07-18 : 21:56:02

Thanks Corey...

UPDATE A
SET Sequence = (select count(*) + 1 from mytable where Sequence < A.Sequence)
FROM MyTable A

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-18 : 22:01:39

DUH! :)

Create Table #tempTable (data nvarchar(10), seq int)
Insert into #tempTable Values('textb',2)
Insert into #tempTable Values('textc',3)
Insert into #tempTable Values('texte',5)

UPDATE tt
SET tt.seq = (SELECT COUNT(seq)+1 FROM #tempTable WHERE seq<tt.seq)
FROM #tempTable tt

SELECT * FROM #tempTable

Drop Table #tempTable

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-18 : 22:03:40

Something wrong??

Corey

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-18 : 22:07:08

The funny thing is I've posted a solution just like this on here several times. lol

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

SamC
White Water Yakist

3467 Posts

Posted - 2004-07-18 : 22:18:17

My excuse: It's Sunday night, I'm tired.

Seems to me there's also a JOIN solution that might be faster (I don't have a performance issue, this is for the heck of it.)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-18 : 22:23:06

Create Table #tempTable (data nvarchar(10), seq int)
Insert into #tempTable Values('textb',2)
Insert into #tempTable Values('textc',3)
Insert into #tempTable Values('texte',5)

Select
A.data,
A.seq,
newSeq = count(*)
From #tempTable as A, #tempTable as B
Where A.seq >= B.seq
Group By A.data, A.seq

Drop Table #tempTable

Corey

SamC
White Water Yakist

3467 Posts

Posted - 2004-07-18 : 22:32:30

Great Select, but it's not an update...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-18 : 22:36:13

I like my example. I just like saying update tt though.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-18 : 22:49:06

yeah yeah... details [shrug]

Corey