mysql - ERROR 1241 (21000) 和 ERROR 1093 (HY000) 阻止执行 MYSQL 子查询

Question

我在这里提供mysql查询:-

mysql> UPDATE tbl_driver SET active_flag=NULL WHERE Pk_driver_id = (SELECT Pk_driver_id FROM tbl_driver JOIN tbl_location ON tbl_driver.pk_driver_id = tbl_location.fk_driver_id WHERE updated_at <= NOW() - INTERVAL 3 MINUTE AND active_flag=1);



ERROR 1093 (HY000): You can't specify target table 'tbl_driver' for update in FROM clause

所以我将子查询更改为选择*:

mysql> UPDATE tbl_driver SET active_flag=NULL WHERE Pk_driver_id = (SELECT * FROM tbl_driver JOIN tbl_location ON tbl_driver.pk_driver_id = tbl_location.fk_driver_id WHERE updated_at <= NOW() - INTERVAL 3 MINUTE AND active_flag=1);



ERROR 1241 (21000): Operand should contain 1 column(s).

请帮我解决这个问题，提前致谢。

Answer 1

使用 INNER JOIN 而不是子选择应该可以使您的更新工作:

UPDATE 
    tbl_driver 
INNER JOIN 
    tbl_location 
ON 
    tbl_driver.pk_driver_id = tbl_location.fk_driver_id
SET tbl_driver.active_flag = NULL
WHERE
    updated_at <= NOW() - INTERVAL 3 MINUTE 
AND 
    tbl_driver.active_flag = 1;

MySQL 当前(版本 5.6)对 use of subqueries in UPDATE statements 有限制:

Currently, you cannot update a table and select from the same table in a subquery.