MYSQL:更新表时出错

Question

我写了下面的查询来更新 mysql 表

update table1
set val_col = 'TRUE'
where id IN(
SELECT ID  
FROM table1 a INNER JOIN
     table2 b
     ON a.a_id = b.a_id
WHERE a.create_dt >= '2017-01-07' AND
      b.check_status = 'FAIL' AND
      b.check_type = 'check1' AND
      b.timestamp_val = (SELECT MAX(b2.timestamp_val)
                         FROM table2 b2
                         WHERE b2.a_id = b.a_id AND
                               b2.check_status = b.check_status AND
                               b2.check_type = b.check_type
                        ));

我收到错误

您无法在 FROM 子句中指定要更新的目标表“table1”

Answer 1

该错误非常清楚，表明您正在尝试更新 table1 但 table1 也在 where 子句中使用。因此，创建一个内部选择并为其添加别名应该可以解决问题；

update table1
set val_col = 'TRUE'
where id IN(
select * from (
SELECT ID  
FROM table1 a INNER JOIN
     table2 b
     ON a.a_id = b.a_id
WHERE a.create_dt >= '2017-01-07' AND
      b.check_status = 'FAIL' AND
      b.check_type = 'check1' AND
      b.timestamp_val = (SELECT MAX(b2.timestamp_val)
                         FROM table2 b2
                         WHERE b2.a_id = b.a_id AND
                               b2.check_status = b.check_status AND
                               b2.check_type = b.check_type
                        )) aliasTable1);