更新数据库时,发生如下错误:

[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