MYSQL - How to UPDATE after SELECT from a table

Question

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?

What you are asking for is not posible with mysql. You would need some programming language to handle the query results and run the UPDATE queries. — Daniel Ramos, Mar 31 '14 at 07:49
@orangehippo it is possible, please see the answer by vhu below — Altiano Gerung, Mar 31 '14 at 13:19

score 4 · Answer 1 · edited Sep 04 '15 at 05:56

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
)

score 3 · Accepted Answer · answered Mar 31 '14 at 08:20

3

You can also do the following:

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

answered Mar 31 '14 at 08:20

vhu

12,244
11
38
48

This was very useful - i just forget that i can use LIMIT option in update query. That with order by keyword can complete relatively complex task with easy :) Thank you – xentia Oct 13 '15 at 15:03

MYSQL - How to UPDATE after SELECT from a table

2 Answers2