Mysql in PHP - how to update only one row in table but with greatest id number
The use of MAX()
is not possible at this position. But you can do this:
UPDATE table SET name='test_name' ORDER BY id DESC LIMIT 1;
For multiple table, as @Euthyphro question, use table.column
.
The error indicates that column id
is ambiguous.
Example :
UPDATE table1 as t1
LEFT JOIN table2 as t2
ON t2.id = t1.colref_t2
SET t1.name = nameref_t2
ORDER BY t1.id DESC
LIMIT 1
UPDATE table SET name='test_name' WHERE id = (SELECT max(id) FROM table)
This query will return an error as you can not do a SELECT subquery from the same table you're updating.
Try using this:
UPDATE table SET name='test_name' WHERE id = (
SELECT uid FROM (
SELECT MAX(id) FROM table AS t
) AS tmp
)
This creates a temporary table, which allows using same table for UPDATE and SELECT, but at the cost of performance.