Roberts Rakvics Roberts Rakvics - 6 months ago 25
SQL Question

MySql select last record and update it

I want to select the last record in the table and update its name.

UPDATE item
SET name = (SELECT name FROM pds
WHERE id = 9)
WHERE id=(SELECT id ORDER BY id DESC LIMIT 1);


However, when executing name is changed for all the records.

Tried also

UPDATE item
SET name = (SELECT name FROM pds
WHERE id = 9)
WHERE id=(SELECT id FROM item ORDER BY id DESC LIMIT 1);


Any suggestion?

Answer

In MySQL you can apply order by and limit clauses to an update statement:

UPDATE item 
SET name = (SELECT name FROM pds
            WHERE id = 9)
ORDER BY id DESC
LIMIT 1