MySQL 数据库 [Err] 1093 - You can't specify target table 'd_alarm' for update in FROM clause

weixin_33755847

于 2016-05-18 13:53:23 发布

阅读量110

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：https://yq.aliyun.com/articles/47265

本文解决了MySQL中尝试更新表时出现的错误[Err]1093，并提供了一个具体的例子来展示如何通过更改SQL语句来避免这个错误。

更新数据库时，发生如下错误：

[Err] 1093 - You can't specify target table 'd_alarm' for update in FROM clause

SQL 语句为：

UPDATE d_alarm SET alarmEndTime = '2015-09-11 11:00:24.17' WHERE  (select  case when measurePointId  is null  then 0  else measurePointId end as measurePointId from d_alarm)  = null  AND alarmCode = '29' AND eId = 22

解决方法：

把要更新的几列数据查询出来做为一个第三方表，然后筛选更新。

改为：

UPDATE d_alarm d1 SET d1.alarmEndTime = '2015-09-11 11:00:24.17' WHERE 
 (select  case when d2.measurePointId  is null  then 0  else d2.measurePointId end as measurePointId from (SELECT * FROM d_alarm) d2) =0 AND alarmCode = '29' AND eId = 22