php PHP中的Mysql - 如何仅更新表中的一行但具有最大的ID号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9220497/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql in PHP - how to update only one row in table but with greatest id number
提问by Kalreg
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:
我正在尝试更新我的数据库中的字段，但遇到了这样一个简单的问题：我只想更新表中具有最大 ID 号的一行。我会做这样的事情：
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.
我只想更新最后一行，因为 ID 号是最大的。
采纳答案by Muhammad Abrar
Select the max id first, then update.
首先选择最大id，然后更新。
UPDATE table SET name='test_name' WHERE id = (SELECT max(id) FROM table)
UPDATE table SET name='test_name' WHERE id = (SELECT max(id) FROM table)
回答by iblue
The use of MAX()is not possible at this position. But you can do this:
MAX()在此位置无法使用。但是你可以这样做：
UPDATE table SET name='test_name' ORDER BY id DESC LIMIT 1;
回答by Lachlan McD.
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.
我认为 iblue 的方法可能是您最好的选择；但另一种解决方案可能是将结果设置为变量，然后在 UPDATE 语句中使用该变量。
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.
如果您希望使用相同的 ID 运行多个查询，这可能会派上用场，但如果您只执行一个更新操作，则运行两个查询并不是很理想。
回答by Bryn
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.
此查询将返回错误，因为您无法从正在更新的同一个表中执行 SELECT 子查询。
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.
这会创建一个临时表，它允许对 UPDATE 和 SELECT 使用相同的表，但以性能为代价。
回答by Yanks
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.
因为你不能用SELECT IN DELETE OR UPDATE CLAUSE.ORDER BY ID DESC LIMIT 1。这为您提供了具有最大值的 ID，MAX(ID)就像您尝试做的那样。但MAX(ID)不会工作。
回答by WaveTyler
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';
回答by kishan Radadiya
We can update the record using max()function and maybe it will help for you.
我们可以使用max()函数更新记录，也许它会对您有所帮助。
UPDATE MainTable
SET [Date] = GETDATE()
where [ID] = (SELECT MAX([ID]) FROM MainTable)
It will work the perfectfor me.
它对我来说是完美的。
回答by Jorge Coral Torres
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
)
回答by Andrew
Using PHP I tend to do run a mysqli_num_rowsthen put the result into a variable, then do an UPDATEstatement saying where ID = the newly created variable. Some people have posted there is no need to use LIMIT 1on 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.
使用 PHP 我倾向于运行 amysqli_num_rows然后将结果放入一个变量中，然后做一个UPDATE声明，说明其中 ID = 新创建的变量。有些人发帖说LIMIT 1最后不需要使用，但我喜欢这样做，因为它不会造成任何微不足道的延迟，但可以防止采取任何不可预见的行动。
If you have only just inserted the row you can use PHP's mysqli_insert_idfunction to return this id automatically to you without needing to run the mysqli_num_rowsquery.
如果您刚刚插入了行，您可以使用 PHP 的mysqli_insert_id函数自动返回此 ID，而无需运行mysqli_num_rows查询。
