MySQL select last record and update it

Question

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);

Answer 1

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

Answer 2

Just use limit and order by:

update item
    set name = (select name from pds where id = 9)
    order by id desc
    limit 1;

Your second version should work . . . except MySQL probably generates a syntax error. Try:

UPDATE item i CROSS JOIN
       (SELECT MAX(i2.id) as maxid FROM item i2) mi
    SET i.name = (SELECT pds.name FROM pds WHERE pds.id = 9)
    WHERE i.id = mi.maxid;

The first version is syntactically correct, but it has no FROM clause, so it is using the id value from the outer query -- and just checking that the id is equal to itself. When using subqueries in the select or where clauses, always use qualified column names. I advise doing this all the time, but this is especially important for subqueries in these clauses.