欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Table ‘m‘ is specified twice, both as a target for ‘UPDATE‘ and as a separate source for data

程序员文章站 2022-05-26 17:55:39
...
update TTRD_SET_INSTRUCTION m
   set m.state = case when m.state = -99999 then 0 else -m.state end
   where m.inst_id in (
     select inst_id from TTRD_SET_INSTRUCTION m
     inner join TTRD_OTC_TRADE t on m.trade_id = t.intordid and m.inst_type not in (2,50) and m.state < 0
     inner join TTRD_OTC_TRADE t2 on t2.his_ref_tradeid = t.sysordid
     where t2.sysordid = 12345
   );

执行以上的语句会报错:

[Err] 1093 - Table 'm' is specified twice, both as a target for 'UPDATE' and as a separate source for data

以上报错的原因为:TTRD_SET_INSTRUCTION 既作为更新表,由作为子查询的表。在mysql官方文档中有如下一段(参考UPDATE Syntax篇章):
You cannot update a table and select from the same table in a subquery.
意思也就是不允许更新一个表同时在子查询语句中查询同一张表。

通常update语句如下

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET assignment_list
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
value:
{expr | DEFAULT}
assignment:
col_name = value
assignment_list:
assignment [, assignment] ...

mysql提供了一种多表的语法

UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET assignment_list
[WHERE where_condition]

用这个多表语句可以解决上面的问题(更新表与其他表JOIN的问题),以下为官方案例

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

所以修改以上的语句如下所示

UPDATE TTRD_SET_INSTRUCTION m,TTRD_OTC_TRADE t,TTRD_OTC_TRADE t2
set m.state = case when m.state = -99999 then 0 else -m.state end
WHERE m.trade_id = t.intordid and m.inst_type not in (2,50) and m.state < 0
and t2.his_ref_tradeid = t.sysordid and t2.sysordid = 12345;

这样执行是没有问题的。

如果仅仅支持mysql,以上语法就足够了。如果需要同时支持oracle、mysql语法,以上还是不够的。既然不能同时针对同一个表声明两次,那么在where中的select子语句中不再声明要更新的表。修改如下

update TTRD_SET_INSTRUCTION m
    set m.state = case when m.state = -99999 then 0 else -m.state end
    where exists (
      select 1 from TTRD_OTC_TRADE t
			inner join TTRD_OTC_TRADE t2 on t2.his_ref_tradeid = t.sysordid
      where m.trade_id = t.intordid and m.inst_type not in (2,50) and m.state < 0
      and t2.sysordid = 12345
    );

前后对比如下
Table ‘m‘ is specified twice, both as a target for ‘UPDATE‘ and as a separate source for data