MYSQL - How to UPDATE after SELECT from a table

Altiano Gerung picture Altiano Gerung · Mar 31, 2014 · Viewed 7k times · Source

i want to UPDATE the a column after i SELECT a table

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;

i got just 2 rows that i want to be updated how can i UPDATE these rows?

Answer

M Khalid Junaid picture M Khalid Junaid · Mar 31, 2014

You can use a nested select in your update query,note it will update your rows with same value not with a different value for each row

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
)