MYSQL - 如何从表中选择后进行更新

Question

我想在选择表后更新 a 列

SELECT id_copies, id_shop, id_dvd 
FROM dvd_copies
WHERE id_dvd = '001-192.168.1.103-6' AND id_shop='002'
ORDER BY id_copies DESC
LIMIT 2;

我只有 2 行想要更新如何更新这些行？

Answer 1

您可以在

更新查询中使用嵌套选择，请注意它将使用相同的值更新您的行每行的值不同

UPDATE dvd_copies 
SET your_column_to_update ='your value'
WHERE id_copies IN(
    SELECT t.id_copies FROM 
    (SELECT id_copies
        FROM dvd_copies 
        WHERE id_dvd = '001-192.168.1.103-6' AND id_toko='002'
        ORDER BY id_copies DESC LIMIT 2
    ) t
)

Answer 2

您还可以执行以下操作：

UPDATE dvd_copies
SET your_column_to_update ='your value'
WHERE id_dvd = '001-192.168.1.103-6' AND id_toko='002'
ORDER BY id_copies DESC
LIMIT 2