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/

Go to Top of Page

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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

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

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

Go to Top of Page
   

- Advertisement -