Mysql in PHP - how to update only one row in table but with greatest id number
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.
I think iblue's method is probably your best bet; but another solution might be to set the result as a variable, then use that variable in your UPDATE statement.
SET @max = (SELECT max(`id`) FROM `table`);UPDATE `table` SET `name` = "FOO" WHERE `id` = @max;This could come in handy if you're expecting to be running multiple queries with the same ID, but its not really ideal to run two queries if you're only performing one update operation.