SQL：在嵌套 FROM 子句的 UPDATE 语句中使用目标表
我有一个包含以下列的 (mysql) 数据库表：
NAME | String (Unique)
STATUS | int
UPDATE_COUNT | int (Unique)
我希望 Max(UPDATE_COUNT) 的值反映对表中的行执行的累积更新数。 例如，从空表开始：
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。
这个想法是“选择max(update_count) from mytable" 结果表示执行的插入/更新总数。
我想编写自动增加 update_count 值的插入和更新 sql 语句，例如：
"UPDATE MYTABLE SET STATUS=1,UPDATE_COUNT=(SELECT MAX(UPDATE_COUNT) FROM MYTABLE) WHERE NAME IN ('John', 'Mary')"
但是这不是有效的 sql，更新语句可能不包含从同一个表中选择的 from 子句，mysql 中的错误是：
"You can't specify target table 'MYTABLE' for update in FROM clause"
Any关于如何规避此限制的建议？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
刚刚在优秀的 Xaprb 博客上找到了以下解决方案：
http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/
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/