MySQL更新status列报错：无法在FROM子句指定更新目标表

先创建测试表并插入数据：

CREATE TABLE log_table (id INTEGER, street TEXT, status INTEGER);

插入测试数据：

INSERT INTO log_table (`id`, `street`, `status`) 
VALUES ('1', 'main_street', '0'), 
       ('2', '1st_street', '0'), 
       ('3', '1st_street', '0'), 
       ('4', 'main_street', '0'), 
       ('5', '2nd_street', '0'), 
       ('6', '1st_street', '0'), 
       ('7', 'main_street', '0'), 
       ('8', '2nd_street', '0');

需求是将status列更新为对应street字段的出现次数，例如main_street出现3次，对应行的status需设为3。

尝试执行的更新语句及报错：

UPDATE log_table l1 SET status = (SELECT COUNT(*) FROM log_table l2 WHERE l2.street = l1.street);

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

问题原因 #

MySQL不允许在UPDATE/DELETE操作的子查询中直接引用正在被修改的目标表。这是因为InnoDB事务机制下，更新过程中表数据处于变动状态，直接引用会引发锁冲突或数据一致性问题，因此MySQL做了这个限制。

解决方案 #

提供两种可行的实现方式：

方法1：将统计子查询包装为派生表 #

通过将统计结果转为临时派生表，绕开直接引用目标表的限制：

UPDATE log_table l1 
SET status = (
    SELECT cnt FROM (
        SELECT street, COUNT(*) AS cnt 
        FROM log_table 
        GROUP BY street
    ) AS street_counts 
    WHERE street_counts.street = l1.street
);

方法2：使用JOIN方式更新（性能更优） #

通过JOIN关联统计结果表进行更新，效率比子查询更高：

UPDATE log_table l1
JOIN (
    SELECT street, COUNT(*) AS cnt 
    FROM log_table 
    GROUP BY street
) AS street_counts ON l1.street = street_counts.street
SET l1.status = street_counts.cnt;

内容的提问来源于stack exchange，提问作者berkin_ozdemir