CODEWITHSUNDEEP
phpmysqlmax
Kalreg
Kalreg

Reputation: 941

Mysql in PHP - how to update only one row in table but with greatest id number

I am trying to update fields in my DB, but got stuck with such a simple problem: I want to update just one row in the table with the biggest id number. I would do something like that:

UPDATE table SET name='test_name' WHERE id = max(id)

Unfortunatelly it doesnt work. Any ideas?

Table Structure

id | name
---|------
 1 | ghost
 2 | fox
 3 | ghost

I want to update only last row because ID number is the greatest one.

Upvotes: 21

Views: 43713

Answers (9)

iblue
iblue

Reputation: 30434

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

Upvotes: 75

Jorge Coral Torres
Jorge Coral Torres

Reputation: 11

I have to update a table with consecutive numbers.

This is how i do. 

UPDATE pos_facturaciondian fdu 
            SET fdu.idfacturacompra = '".$resultado["afectados"]."', 
            fdu.fechacreacion = '".$fechacreacion."'
            WHERE idfacturaciondian = 
            (
                SELECT min(idfacturaciondian) FROM 
                (   
                    SELECT * 
                    FROM pos_facturaciondian fds
                    WHERE fds.idfacturacompra = ''
                    ORDER BY fds.idfacturaciondian
                ) as idfacturaciondian
            )

Upvotes: 0

kishan Radadiya
kishan Radadiya

Reputation: 832

We can update the record using max() function and maybe it will help for you.

 UPDATE MainTable
 SET [Date] = GETDATE()
 where [ID] = (SELECT MAX([ID]) FROM MainTable)

It will work the perfect for me.

Upvotes: 0

WaveTyler
WaveTyler

Reputation: 11

Old Question, but for anyone coming across this you might also be able to do this:

UPDATE
     `table_name` a
JOIN (SELECT MAX(`id`) AS `maxid` FROM `table_name`) b ON (b.`maxid` = a.`id`)
SET a.`name` = 'test_name';

Upvotes: 1

Yanks
Yanks

Reputation: 173

UPDATE table_NAME
SET COLUMN_NAME='COLUMN_VALUE' 
ORDER BY ID 
DESC LIMIT 1;

Because you can't use SELECT IN DELETE OR UPDATE CLAUSE.ORDER BY ID DESC LIMIT 1. This gives you ID's which have maximum value MAX(ID) like you tried to do. But MAX(ID) will not work.

Upvotes: 2

Bryn
Bryn

Reputation: 61

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.

Upvotes: 6

Andrew
Andrew

Reputation: 19

Using PHP I tend to do run a mysqli_num_rows then put the result into a variable, then do an UPDATE statement saying where ID = the newly created variable. Some people have posted there is no need to use LIMIT 1 on the end however I like to do this as it doesn't cause any trivial delay but could prevent any unforeseen actions from being taken.

If you have only just inserted the row you can use PHP's mysqli_insert_id function to return this id automatically to you without needing to run the mysqli_num_rows query.

Upvotes: -2

Lachlan McDonald
Lachlan McDonald

Reputation: 2434

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.

Upvotes: 4

Muhammad Abrar
Muhammad Abrar

Reputation: 2322

Select the max id first, then update.

UPDATE table SET name='test_name' WHERE id = (SELECT max(id) FROM table)

Upvotes: -6

Related Questions