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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SamC
White Water Yakist

USA
3467 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
3467 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
3467 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
3467 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
3467 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  
 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.09 seconds. Powered By: Snitz Forums 2000