Table column 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

General SQL Server Forums

New to SQL Server Programming

Table column update

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,Val
1 1 xxx
2 NULL yyy
3 NULL zzz
4 2 ddd
5 NULL ttt
6 NULL uuu
7 3 hhh
8 NULL HYU

i wanted to updated the second column with NULL values with the previous LineNo

expected output:
1 1 xxx
2 1 yyy
3 1 zzz
4 2 ddd
5 2 ttt
6 2 uuu
7 3 hhh
8 3 HYU

Kindly 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 t
SET t.LineNo= t1.PrevLineNo
FROM table t
CROSS APPLY (SELECT MAX(LineNo) AS prevLineNo
FROM table
WHERE id < t.id
AND LineNo IS NOT NULL
)t1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 t
SET t.LNo= t1.PrevLineNo
FROM dbo.Header t
CROSS APPLY (SELECT MAX(LNo) AS prevLineNo
FROM dbo.Header
WHERE id < t.id
AND LNo IS NOT NULL
)t1
WHERE t.Lno is null

Thanks once again,
regards
Aksaa

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-06 : 11:10:13

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources