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

Transact-SQL (2005)

Update

Author

Topic

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-02-23 : 09:30:53

I'm stuck with the problem that looks very simple, but ...

I have a table:
ID LineNumber CaseNumber
1------ 01------ 286
2------ 01------ 287
3------ 02------ 288
4------ 81------ 287
5------ 83------ 288
6------ 85------ 288
7------ 83------ 290

If this table has miltiple records with the same CaseNumber
I have to update LineNumber in these records with the smallest value,
so my result should look like:

ID LineNumber CaseNumber
1------ 01------ 286
2------ 01------ 287
3------ 02------ 288
4------ 01------ 287 updated
5------ 02------ 288 updated
6------ 02------ 288 updated
7------ 83------ 290

Please help.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-23 : 09:35:41

Try this:

Update t1
Set LineNumber = t2.LineNumber
From tbl t1
JOIN (select CaseNumber, Min(LineNumber) as LineNumber From tbl
Group by CaseNumber) as t2
on t1.CaseNumber = t2.CaseNumber

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-02-23 : 09:43:46

It does the job.

Thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources