mysql - 选择 id 列表并更新这些行

Question

我有以下 MySQL 语句:

UPDATE xxx_iop_user_plans
SET state = 1, accepted = 1
WHERE id IN (SELECT iup1.id FROM xxx_iop_user_plans iup1
INNER JOIN xxx_iop_user_ed iue ON iup1.user_id = iue.user_id
WHERE iue.parent_id = 44 AND iup1.iop_id = 11)

我应该如何更新此语句以更新 SELECT 语句返回的列表中存在 ID 号的行？

Answer 1

我希望你的更新声明能做到这一点。如果它没有更新所有这些 ID，它在做什么？您可以重新组织查询以将 select 子查询放入 from 子句中:

UPDATE x 
SET state = 1, accepted = 1
FROM xxx_iop_user_plans x 
    INNER JOIN (
        SELECT iup1.id 
        FROM xxx_iop_user_plans iup1
            INNER JOIN xxx_iop_user_ed iue ON iup1.user_id = iue.user_id
        WHERE iue.parent_id = 44 AND iup1.iop_id = 11
    ) y ON x.ID = y.ID

但我不认为它的行为与您已有的有任何不同。还是试试看吧。