CODEWITHSUNDEEP
mysqlsql
Node.JS
Node.JS

Reputation: 1592

SQL update column and use max value of a column for each row

I am trying to update Program table and use max value of program.programOrder + 1 for each row. programOrder has to be unique so I don't want to use the same value for all updates and ideally I want to recalculate max value for each row.

I get the following error:

You can't specify target table 'p' for update in FROM clause

update Program p
set p.programOrder = (select max(programOrder) + 1 from Program)
where isActive = false;

I appreciate any help or hint.

Upvotes: 0

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

There are different ways to do this. Probably the simplest uses variables. That is something like this:

select @rn := max(programOrder)
from program;

update Program p
    set p.programOrder = (@rn := @rn + 1)
    where isActive = false;

Note that such use of variables is deprecated in the most recent version of MySQL, but this is still probably the most convenient method.

Upvotes: 2

Related Questions