CODEWITHSUNDEEP
mysqlsql-updatecorrelated-subquery
Mathias Bak
Mathias Bak

Reputation: 5145

How to update based on anoter row in the same table?

I want to create a query that updates an int based on the int of the row with an id that is 1 higher.

I have tried this query, but it says that i can't label the table in an update statement. But how do i reference it in my subquery?

update t1 a set `int1` = (select `int1` from t1 b where b.id=a.id+1);

How can I overcome that I can't use an alias?

Upvotes: 1

Views: 74

Answers (1)

Devart
Devart

Reputation: 122042

Try this one -

UPDATE
  t1 a
  JOIN t1 b
    ON b.id = a.id + 1
SET
  a.int1 = b.int1;

If there are holes in id values, the query may be changed.

Upvotes: 2

Related Questions