SQL:在嵌套FROM子句的UPDATE语句中使用目标表 [英] SQL : Using the target table in an UPDATE statement in a nested FROM clause

问题描述

我有一个(mysql)数据库表，其中包含以下列:

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

NAME | String (Unique)

STATUS | int

UPDATE_COUNT | int (Unique)

我希望Max(UPDATE_COUNT)的值反映在表中的行上执行的累积更新数.例如，从一个空表开始:

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

等.

因此，对于任何行，无论是已更新还是已插入，每行插入或更新的已插入更新计数的值均为max(update_count)+ 1.

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.

从mytable中选择max(update_count)"结果表示代表执行的插入/更新总数.

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

我想编写插入和更新sql语句，以自动增加update_count的值，例如:

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')"

但是这不是有效的sql，一条更新语句可能不包含从同一表中选择的from子句，mysql中的错误是:

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?

推荐答案

在出色的Xaprb博客上找到了以下解决方案:

Just found the following solution on the excellent Xaprb blog:

查看全文