SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Puzzle
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SamC
White Water Yakist

USA
3460 Posts

Posted - 07/18/2004 :  20:07:01  Show Profile  Reply with Quote
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  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

SamC
White Water Yakist

USA
3460 Posts

Posted - 07/18/2004 :  21:22:09  Show Profile  Reply with Quote
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.
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 07/18/2004 :  21:32:59  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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
Go to Top of Page

SamC
White Water Yakist

USA
3460 Posts

Posted - 07/18/2004 :  21:56:02  Show Profile  Reply with Quote
Thanks Corey...

UPDATE A
SET Sequence = (select count(*) + 1 from mytable where Sequence < A.Sequence)
FROM MyTable A
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/18/2004 :  22:01:39  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 07/18/2004 :  22:03:40  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
Something wrong??

Corey
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/18/2004 :  22:07:08  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

SamC
White Water Yakist

USA
3460 Posts

Posted - 07/18/2004 :  22:18:17  Show Profile  Reply with Quote
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.)
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 07/18/2004 :  22:23:06  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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
Go to Top of Page

SamC
White Water Yakist

USA
3460 Posts

Posted - 07/18/2004 :  22:32:30  Show Profile  Reply with Quote
Great Select, but it's not an update...
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/18/2004 :  22:36:13  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
I like my example. I just like saying update tt though.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 07/18/2004 :  22:49:06  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
yeah yeah... details [shrug]

Corey
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000