CODEWITHSUNDEEP
mysql
Paul
Paul

Reputation: 3

MySQL : Updating a table having a 'where' clause with a max value

I want to update the last generated row (the max(id) row.

I tried this code but it doesn't work 

update person t1
set t1.age = 25
where t1.id = (select max(t2.id)  from person t2
            where t2.address = 'LA, California');

MySQL tells me that : Error Code: 1093. You can't specify target table 't1' for update in FROM clause

So, I suppose that I cannot reach the same tale while performing operations such as updates.

How can I sole this problem ?

Regards.

Upvotes: 0

Views: 3286

Answers (2)

Zane Bien
Zane Bien

Reputation: 23125

You cannot reference the same table in a subquery, but you can instead do it in a JOIN (which is allowed in UPDATE and DELETE statements):

UPDATE person a
JOIN   (SELECT MAX(id) AS id FROM person WHERE address = 'LA, California') b
       ON a.id = b.id
SET    a.age = 25

Another way you can do it is by using the ORDER BY / LIMIT technique:

UPDATE   person
SET      age = 25
WHERE    address = 'LA, California'
ORDER BY id DESC
LIMIT    1

Upvotes: 2

Omesh
Omesh

Reputation: 29111

You can try as:

UPDATE person t1
    INNER JOIN (SELECT MAX(id) AS id  FROM person
            WHERE t2.address = 'LA, California') t2
        ON t1.id = t2.id
SET t1.age = 25;

or

SELECT MAX(t2.id)
INTO @var_max_id
FROM person t2
WHERE t2.address = 'LA, California';

UPDATE person t1
SET t1.age = 25
WHERE t1.id = IFNULL(@var_max_id, -1);

Upvotes: 2

Related Questions