SQL – updating column based on next row value

I have a table, and the requirement is to update column ‘val’ based on the value in the column ‘val’ in next row (ordered by by id).

Table is shown below :

+----+------+------------+
| id | val  | dt         |
+----+------+------------+
|  1 | k    | 2021-01-01 |
|  3 | k    | 2021-05-15 |
|  5 | k2   | 2021-10-11 |
+----+------+------------+

I’m able get the select query to get the next value, however the update query is not working.

Here is the select query (working fine):

select t1.id, t1.val as t1val, t2.val as t2val from ssc t1 inner join (select id, lead(val) over(order by id) as val from ssc) t2 on (t1.id=t2.id); 

Here is the update query, not working :

UPDATE t 
SET t.val = t2.val 
FROM ssc t 
INNER JOIN (select id, lead(val) over(order by id) as val from ssc) t2 ON t.Id = t2.Id

Any inputs on how to fix this ? tia!

Answer

Your order of clauses are a bit off, the following modification will work

UPDATE ssc t 
INNER JOIN (select id, lead(val) over(order by id) as val from ssc) t2 ON t.Id = t2.Id
SET t.val = t2.val 

View Live Demo

You may consider using the following query which also uses the lead function but without the join.

Schema (MySQL v8.0)

CREATE TABLE ssc (
  `id` INTEGER,
  `val` VARCHAR(2),
  `dt` DATETIME
);

INSERT INTO ssc
  (`id`, `val`, `dt`)
VALUES
  ('1', 'k', '2021-01-01'),
  ('3', 'k1', '2021-05-15'),
  ('5', 'k2', '2021-10-11');

---

Query #1

select * from ssc;

id	val	dt
1	k	2021-01-01 00:00:00
3	k1	2021-05-15 00:00:00
5	k2	2021-10-11 00:00:00

---

Query #2

UPDATE ssc t1,
   (
       SELECT t2.id,LEAD(t2.val) OVER (ORDER BY t2.id) as val
       FROM ssc t2
   ) t3
SET t1.val = t3.val
WHERE t1.id = t3.id;

There are no results to be displayed.

---

Query #3

select * from ssc;

id	val	dt
1	k1	2021-01-01 00:00:00
3	k2	2021-05-15 00:00:00
5		2021-10-11 00:00:00

---

View on DB Fiddle

Let me know if this works for you.