| Author |
 Topic  |
|
|
SamC
White Water Yakist
USA
3459 Posts |
 Posted - 07/18/2004 : 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
USA
4184 Posts |
Posted - 07/18/2004 : 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
USA
3459 Posts |
Posted - 07/18/2004 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 07/18/2004 : 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
USA
3459 Posts |
Posted - 07/18/2004 : 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
USA
4184 Posts |
Posted - 07/18/2004 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 07/18/2004 : 22:03:40
|
Something wrong??  
Corey |
|
|
|
derrickleggett
Pointy Haired Yak DBA
USA
4184 Posts |
Posted - 07/18/2004 : 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
USA
3459 Posts |
Posted - 07/18/2004 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 07/18/2004 : 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
USA
3459 Posts |
Posted - 07/18/2004 : 22:32:30
|
| Great Select, but it's not an update... |
|
|
|
derrickleggett
Pointy Haired Yak DBA
USA
4184 Posts |
Posted - 07/18/2004 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 07/18/2004 : 22:49:06
|
yeah yeah... details [shrug]
Corey |
|
|
| |
Topic |
|
Puzzle
