Hi I have a table that has a structure like below
| Id | oldid | newid | dateon | currentdate | code |
|---|---|---|---|---|---|
| 1 | NULL | 636 | 2022-03-07 16:02:48.960 | 2022-03-25 10:27:56.393 | 777 |
| 2 | 636 | 202 | 2022-03-25 10:27:56.393 | 2022-05-11 14:34:48.153 | 777 |
| 3 | 202 | 203 | 2022-05-11 14:34:48.153 | 2022-05-12 14:35:42.957 | 777 |
| 4 | 203 | 273 | 2022-05-12 14:35:42.957 | 2022-05-14 14:35:42.957 | 777 |
| 5 | 273 | 189 | 2022-05-14 14:35:42.957 | NULL | 777 |
Currently the column in currentdate is empty. I want to update the column of current date like mentioned above i.e update column currentdate one step back from dateon column.
I tried this query, But it is updating random data
UPDATE a
SET a.currentdate = b.dateon
FROM Table a
LEFT JOIN Table b ON b.code = a.c aodend b.oldid = a.newid
CodePudding user response：
You could use the lead function within CTE as the following:
with cte as
(
select *,
lead(dateon) over (partition by code order by dateon) ld
from table_name
)
update cte set currentdate = ld;