Getting nextcol value using 1 query 1

[rp1504]

In Sql Server, if I want to update a column based on the next row data from another column in 1 query how is this possible ?

e.g. :

I have a data like this : I want to populate ColumnC with ColumnB next row data.
Can I write a dynamic query that would be used to populate ColumnC otherwise I have to use Cursor.

ColumnA | ColumnB | ColumnC
---------------------------------------------
X 10 20
X 20 30
X 30 40
X 40 NULL
Y 25 40
Y 40 NULL
Z 35 39
Z 39 45
Z 45 NULL

 

[hmckillop]

I included the scripts I used to create the data, to be fair someone else in here came up with the solution , so cant claim all credit

CREATE table aa_test
(
--MyID int identity(1,1),
mycolA char(1),
mycolB int,
myColC int
)

insert into aa_test (mycolA, myColB, mycolC) values('X', 10, 11)
insert into aa_test (mycolA, myColB, mycolC) values('X', 20, 21)
insert into aa_test (mycolA, myColB, mycolC) values('X', 30, 31)
insert into aa_test (mycolA, myColB, mycolC) values('Y', 10, 11)
insert into aa_test (mycolA, myColB, mycolC) values('Y', 20, 21)
insert into aa_test (mycolA, myColB, mycolC) values('Y', 30, 31)
insert into aa_test (mycolA, myColB, mycolC) values('Z', 10, 11)
insert into aa_test (mycolA, myColB, mycolC) values('Z', 20, 21)
insert into aa_test (mycolA, myColB, mycolC) values('Z', 30, 31)
select * from aa_test
order by mycola, mycolb

UPDATE aa_test
SET myColC = (SELECT TOP 1 newval.myColB FROM aa_test newval WHERE aat.myColA = newval.myColA AND aat.myColB < newval.myColB ORDER BY newval.myColB ASC)
FROM aa_test aat


select * from aa_test
order by mycola, mycolb

See if this is what you want.


"I'm living so far beyond my income that we may almost be said to be living apart