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.
Author |
Topic |
Aksaa
Starting Member
3 Posts |
Posted - 2013-02-04 : 00:58:39
|
Hi all, I have a table with three columns ex.id,LineNo,Val1 1 xxx2 NULL yyy3 NULL zzz4 2 ddd5 NULL ttt6 NULL uuu7 3 hhh8 NULL HYUi wanted to updated the second column with NULL values with the previous LineNoexpected output:1 1 xxx2 1 yyy3 1 zzz4 2 ddd5 2 ttt6 2 uuu7 3 hhh8 3 HYUKindly advise me how can i update the same table with query without using cursor.regards,Aksaa |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-04 : 01:02:52
|
[code]UPDATE tSET t.LineNo= t1.PrevLineNoFROM table tCROSS APPLY (SELECT MAX(LineNo) AS prevLineNo FROM table WHERE id < t.id AND LineNo IS NOT NULL )t1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Aksaa
Starting Member
3 Posts |
Posted - 2013-02-06 : 05:05:17
|
Hi visakh,Thanks for the script. with a minor change i was able to get the required output:final query is:UPDATE tSET t.LNo= t1.PrevLineNoFROM dbo.Header tCROSS APPLY (SELECT MAX(LNo) AS prevLineNo FROM dbo.Header WHERE id < t.id AND LNo IS NOT NULL )t1 WHERE t.Lno is nullThanks once again,regardsAksaa |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-06 : 11:10:13
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|