I have a database table (mysql) with the following columns:
NAME | String (Unique)
STATUS | int
UPDATE_COUNT | int (Unique)
I want the Max value (UPDATE_COUNT) to reflect the cumulative number of updates performed by the 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 updated or inserted, the value of the number of updates inserted or updated by each row is max (update_count) + 1.
The idea is that the “select max (update_count) of mytable” result represents the total number of inserts / updates completed.
I would like to write inserts and updates of SQL statements that automatically increment update_count, for example:
"UPDATE MYTABLE SET STATUS=1,UPDATE_COUNT=(SELECT MAX(UPDATE_COUNT) FROM MYTABLE) WHERE NAME IN ('John', 'Mary')"
sql, update my from , mysql:
"You can't specify target table 'MYTABLE' for update in FROM clause"
, ?