mysql - 如何将 UPDATE 查询与子查询相结合？

Question

在我的 Mysql 数据库中，我想为使用此选择语句找到的所有记录更新字段 fup:

SELECT ic.hash
FROM incompany as ic, app
WHERE ic.id = app.ic_id;

所以我创建了以下组合查询:

UPDATE incompany
SET fup = 'x' 
WHERE hash IN (SELECT ic.hash
FROM incompany as ic, app
WHERE ic.id = app.ic_id);

但是这个查询给了我以下错误:

You can't specify target table 'incompany' for update in FROM clause

有谁知道我该怎么做？欢迎所有提示!

Answer 1

您似乎需要两列的条件，所以这有点棘手。如果我正确地遵循逻辑:

UPDATE incompany ic JOIN
       (SELECT DISTINCT ic.hash
        FROM incompany ic JOIN
             app
             ON ic.id = app.ic_id 
       ) ica
       ON ica.hash = ic.hash
    SET fup = 'x' ;