CODEWITHSUNDEEP
mysqlsql
MiaadP
MiaadP

Reputation: 3

MySQL: Use select in update query

I have the query:

update movies
    SET name = 'example'
    where id = (select MAX(id) from movies);

and I need update last record.

Currently I get the error:

.#1093 - You can't specify target table 'movies' for update in FROM clause

Upvotes: 0

Views: 70

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

In MySQL, you can use order by and limit:

update movies
    set name = 'example'
    order by id desc
    limit 1;

MySQL has a limitation on update and delete queries where it does not allow the table being referenced to be in a subquery in the set or where clauses.

You could also express this using join:

update movies m join
       (select max(m2.id) from movies m2) m2
       on m2.max_id = m.id
    set m.name = 'example';

But I find order by and limit to be clearer.

Upvotes: 3

forpas
forpas

Reputation: 164069

You can nest the query:

select MAX(id) id from movies

inside another select:

update movies
SET name = 'example'
where id = (select t.id from (select MAX(id) id from movies) t);

Upvotes: 0

Related Questions