MySQL You can't specify target table 'm' for update in FROM clause

我遇到的场景是这样的，现在数据表比赛信息中有几个字段，表示赛事的报名起止日期和比赛起止日期，我需要在时间到达某一天时自动更新赛事状态这一列。

这种肯定就是需要使用到存储过程了，而且需要定义一个事件来定时执行这个存储过程。

# 定义存储过程
CREATE PROCEDURE update_status()
BEGIN
    IF EXISTS(SELECT id FROM `match` WHERE now() > startTime)
        UPDATE `match` SET status=2 WHERE id IN (SELECT id FROM `match` WHERE now() > startTime);
    END IF;
END

按照语法来说，好像并没有什么错误，但是就会报错：You can't specify target table 'match' for update in FROM clause

这句话的意思是：不能先select出同一表中的某些值，再update这个表(在同一语句中)

在网上查找了解到：这个错误只会在MySQL中出现，MSSQL和Oracle中不会出现这个问题

如何解决：

1、先把查询的结果当成另外一个结果集，然后从这个结果集中再进行查询

如上面的存储过程中间的SQL语句可以修改成这样：

UPDATE `match` SET status=2 WHERE id IN (SELECT t.id FROM (SELECT id FROM `match` WHERE now() > startTime) t);

把一开始查询出来的数据当做一个新的数据集t，然后再从数据集中查询，这样就避免了同一个结果集进行更新和查询两种操作。

2、但是发现MySQL5.7版本设置了这个之后好像还是没有用，还是报原来的错误，经过长时间的搜索和总结，最终我找到了适用于这个版本的方法：使用left join 替换

修改原来的代码：

UPDATE `match` m LEFT JOIN `match` mm ON m.id=mm.id SET m.status= 2 WHERE now() > m.enrollmentStartTime;

这个问题最终得到解决。

 

所以最终我的需求的解决完成SQL语句是这样的：

# 定义存储过程
CREATE PROCEDURE update_status()
BEGIN
    IF EXISTS(SELECT id FROM `match` WHERE now() > startTime)
        UPDATE `match` m LEFT JOIN `match` mm ON m.id=mm.id SET m.status=2 WHERE now() > m.enrollmentStartTime;END IF;
END

# 定义作业
CREATE EVENT event_update_status
BEGIN
    CALL update_status()
END

 

下面的链接是和这个问题相关的博客，我也是参考了他们的博客：

https://blog.csdn.net/poetssociety/article/details/82391523

https://stackoverflow.com/questions/51087937/on-update-mysql-row-you-cant-specify-target-table-x-for-update-in-from-claus