Update statement with condition

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 2005 Forums

Transact-SQL (2005)

Update statement with condition

Author

Topic

nasty84
Starting Member

6 Posts

Posted - 2009-11-11 : 15:17:11

For e.g i have the following data
ID GroupId Date Sequence
1 100 10/1/2009 Null
2 100 10/4/2009 Null
3 100 10/5/2099 Null
4 200 11/1/2009 Null
5 200 11/3/2009 null
6 300 11/4/2009 Null
-
-
-
-
-

i need to update the sequence and use the date to get what the Sequence should be. I need to Start with the lowest date for an id and its Sequence would be 1.
For each date after that for the same groupid that is attached to the ID add one to the Sequence number.

i.e, the data should look like this...

ID Groupid Date Sequence
1 100 10/1/2009 1
2 100 10/4/2009 2
3 100 10/5/2099 3
4 200 11/1/2009 1
5 200 11/3/2009 2
6 300 11/4/2009 1
-
-
-
-
-

Is there any way without using Cursors.
Thanks for your help and time..

Sam

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-11 : 15:35:05

select *,
row_number() over (partition by Groupid order by Date) as Sequence
from your_table

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-11 : 15:37:01

Ah! You wanted to update
update t1
set Sequence=dt.Sequence
from your_table t1
join
(select *,
row_number() over (partition by Groupid order by Date) as Sequence
from your_table
)dt
on dt.id = t1.id

No, you're never too old to Yak'n'Roll if you're too young to die.

nasty84
Starting Member

6 Posts

Posted - 2009-11-11 : 16:16:22

Thank you very much.....
i learned a new stuff today.....
i appreciate it.....
once again thanks for ur time and help.....

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-11 : 16:50:00

welcome