Search code examples
mysqlsql-updatecorrelated-subquery

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?

Solution

  • 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.