I want to insert a new row, getting the first next unique rank available. Or update an existing row, changing its rank into the first next unique available.
This works most of the time: [sql]SELECT MAX(rank)+1 FROM funkyTable; # I assign this query's result to $nextRank
INSERT INTO funkyTable (rank) VALUES ($nextRank)
# or:
UPDATE funkyTable SET rank=$nextRank WHERE id=$someId[/sql]
However, this obviously isn't "thread safe". If multiple instances of this script are running simultaneously (which happens to be the case in my situation), another instance may set another row's rank to '$nextRank' just between the select and insert/update queries above.
I don't know if MySQL queries are atomic, but I guess something like this would be safe: [sql]INSERT INTO funkyTable (rank) VALUES ((SELECT MAX(rank)+1 FROM funkyTable))
# or:
UPDATE funkyTable SET rank=(SELECT MAX(rank)+1 FROM funkyTable) WHERE id=$someId[/sql]
But this gives an error:
You can't specify target table 'funkyTable' for update in FROM clause
What's the correct way of doing this?
(Notice there is no Auto Increment on the 'rank' field, there's already Auto Increment on 'id' which is the primary key)