SQL : Using the target table in an UPDATE statement in a nested FROM clause

Question

I have a (mysql) database table with the following columns:

NAME | String (Unique)

STATUS | int

UPDATE_COUNT | int (Unique)

I want the value of Max(UPDATE_COUNT) to reflect the cumulative number of updates performed on rows in the table. For example, starting with an empty table:

Insert - (Name=John, Status=0) -  // update count on that row is set to 1

Insert - (Name=Mary, Status=0) -  // update count on that row is set to 2

Update - (Name=John, Status=1) -  // update count on that row is set to 3

Update - (Name=Mary, Status=2) -  // update count on that row is set to 4

etc..

So for any row, whether updated or inserted, the value of update count that is inserted inserted or updated with each row is max(update_count) + 1.

The idea being that "select max(update_count) from mytable" the result represents the total number of inserts/updates performed.

I would like to write insert and update sql statements that increment the value of update_count automatically, something like:

"UPDATE MYTABLE SET STATUS=1,UPDATE_COUNT=(SELECT MAX(UPDATE_COUNT) FROM MYTABLE) WHERE NAME IN ('John', 'Mary')"

However this is not valid sql, an update statement my not contain a from clause selecting from the same table, the error in mysql is:

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

Any suggestions on how this limitation could be circumvented?

Answer 1

Just found the following solution on the excellent Xaprb blog:

http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/