解决1093 - You can‘t specify target table报错问题及原因分析_ar

报错原因分析

mysql 报错 1093 - you can't specify target table 通常出现在尝试在 update 或 delete 语句的 from 子句中直接引用要更新或删除的目标表。

这是因为 mysql 不允许在同一条 sql 语句中直接对一个表进行更新或删除操作，同时又通过子查询引用该表。

具体原因

直接引用目标表：

当你在 update 或 delete 语句的 where 子句中使用子查询，并且这个子查询直接引用了要更新或删除的表时，就会触发这个错误。

子查询的嵌套：

即使子查询不是直接引用目标表，但如果子查询的结果集包含了目标表的引用，也可能导致这个错误。

解决办法

方法一：使用临时表

将子查询的结果集放入一个临时表中，然后在 update 或 delete 语句中引用这个临时表。（适用数据量小的情况）

-- 创建临时表
create temporary table temp_table as
select id
from your_table
where some_condition;

-- 使用临时表进行更新
update your_table
set column = new_value
where id in (select id from temp_table);

-- 删除临时表
drop temporary table temp_table;

方法二：使用join

使用 join 语句来替代子查询，这样可以避免直接引用目标表。

update your_table t1
join (
    select id
    from your_table
    where some_condition
) t2 on t1.id = t2.id
set t1.column = new_value;

方法三：使用exists

使用 exists 子句来替代 in 子句，这样可以避免直接引用目标表。

update your_table
set column = new_value
where exists (
    select 1
    from your_table t2
    where t2.id = your_table.id
    and some_condition
);

示例

假设有一个表 employees，我们想要更新 salary 字段，条件是 department_id 在某个子查询结果集中。

错误示例

update employees
set salary = 5000
where department_id in (
    select department_id
    from employees
    where department_name = 'sales'
);

正确示例（使用临时表）

create temporary table temp_departments as
select department_id
from employees
where department_name = 'sales';

update employees
set salary = 5000
where department_id in (select department_id from temp_departments);

drop temporary table temp_departments;

正确示例（使用join）

update employees e1
join (
    select department_id
    from employees
    where department_name = 'sales'
) e2 on e1.department_id = e2.department_id
set e1.salary = 5000;

正确示例（使用临时表）

update employees
set salary = 5000
where department_id in (select a.department_id  from(
    select department_id
    from employees
    where department_name = 'sales'
)a);

如果在增删改语句中，要使用子查询的形式进行增删改，那么应该把这个子查询进行第二次select一下并且给上表别名，才可以执行。

这个第二次select实际上就是把第一次的select的结果集放在临时表中。

通过这些方法，可以有效地解决 1093 - you can't specify target table 报错问题。