SQL:在嵌套 FROM 子句中的 UPDATE 语句中使用目标表

2022-01-17 00:00:00 sql-update sql mysql mysql-error-1093

我有一个 (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.

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

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:

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

相关文章