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

Question

我想更新最后生成的行(max(id) 行。

我试过这段代码，但它不起作用

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

MySQL 告诉我:错误代码:1093。您不能在 FROM 子句中指定要更新的目标表 't1'

因此，我想我无法在执行更新等操作时得出相同的故事。

我该如何解决这个问题？

问候。

Answer 1

你可以这样尝试:

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;

或

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