CODEWITHSUNDEEP
mysqlsqlsql-update
Roberts Rakvics
Roberts Rakvics

Reputation: 133

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

Upvotes: 9

Views: 12825

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270493

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.

Upvotes: 3

Mureinik
Mureinik

Reputation: 311883

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

Upvotes: 13

Related Questions