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 |
|
sg2255551
Constraint Violating Yak Guru
274 Posts |
 Posted - 2012-07-12 : 05:46:34
|
| how do i update current row based on previous row? Thanks a lotThe samplePriCol ColA ColB1 1 22 13 1 54 15 2 16 27 2 3The resultPriCol ColA ColB1 1 22 1 23 1 54 1 55 2 16 2 17 2 3 |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2012-07-12 : 06:54:02
|
| CREATE TABLE #UpdateAsPrevious (PriCol INT, ColA INT, ColB INT)INSERT INTO #UpdateAsPreviousSELECT 1, 1, 2 UNION ALLSELECT 2, 1, NULL UNION ALLSELECT 3, 1, 5 UNION ALLSELECT 4, 1, NULL UNION ALLSELECT 5, 2, 1 UNION ALLSELECT 6, 2, NULL UNION ALLSELECT 7, 2, 3SELECT PriCol,ColA, ISNULL(ColB,( SELECT TOP ( 1 ) ColB FROM #UpdateAsPrevious WHERE PriCol < OuterTable.PriCol AND ColB IS NOT NULL ORDER BY PriCol DESC )) AS ColB FROM #UpdateAsPrevious OuterTableORDER BY PriColDROP TABLE #UpdateAsPrevious--------------------------http://connectsql.blogspot.com/ |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-07-12 : 06:55:13
|
something like this if PriCol has no gaps.update aset ColB = b.ColBfrom YourTable as ajoin YourTable as b on a.PriCol=b.PriCol-1where a.ColB is null
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
sg2255551
Constraint Violating Yak Guru
274 Posts |
Posted - 2012-07-12 : 08:48:12
|
| thanks a lot |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-12 : 09:48:57
|
| Will PriceCol be having continuos values always without gaps?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Update Rows based on previous row