MySQL - 选择然后更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24590256/
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 - SELECT then UPDATE
提问by TheWebsiteGuy
I have a script written in PHP which has this line which is working correctly for selecting the data i need;
我有一个用 PHP 编写的脚本，它有这行可以正确地选择我需要的数据；
$result = mysql_query("SELECT product_name, sku, qty FROM supplier_dropship_items WHERE supplier_id = '3' AND status = '2'", $db_beb);
What I'm struggling with is a way to update the records I have selected, once selected I need to change the status = '1'so that the next time my script runs it won't pull the same data in the select and will only pull new items in the table which have status 2.
我正在努力更新我选择的记录的方法，一旦选择，我需要更改，status = '1'以便下次我的脚本运行时它不会在选择中提取相同的数据，只会提取新项目状态为 2 的表。
This is my working result thanks to the comments of the accepted answer below;
由于下面接受的答案的评论，这是我的工作结果；
$result = mysql_query("SELECT id, product_name, sku, qty FROM supplier_dropship_items WHERE supplier_id = '3' AND status = '2' FOR UPDATE", $db_beb);
while($row = mysql_fetch_assoc($result))
{
$sql_table_data[] = $row;
mysql_query("UPDATE supplier_dropship_items SET status=1 WHERE id='".$row['id']."'", $db_beb);
}
采纳答案by Mariano D'Ascanio
If supplier_dropship_itemshas a primary key (it should), then include those fields in the SELECT, then, when you cycle through the results, execute an UPDATEusing the primary key to set the status, as in:
如果supplier_dropship_items有主键（应该），则将这些字段包含在 中SELECT，然后，当您循环查看结果时，UPDATE使用主键执行以设置status，如下所示：
UPDATE supplier_dropship_items SET status=1 WHERE <id_field>=<id_value>;
This assumes you are not executing in an concurrent environment. If you are, then you should lock the records for update, by using SELECT... FOR UPDATE. You can read about it here. As far as I know, this works under MySQL on InnoDB tables.
这假设您不是在并发环境中执行。如果是，那么您应该使用 .lock 来锁定记录以进行更新SELECT... FOR UPDATE。你可以在这里阅读它。据我所知，这在 InnoDB 表上的 MySQL 下有效。
回答by John Ruddell
Just do the UPDATEwhen you SELECTit at the same time.
UPDATE当你SELECT同时做的时候就做。
Change this:
改变这个：
SELECT product_name, sku, qty
FROM supplier_dropship_items
WHERE supplier_id = '3' AND status = '2';
to this:
对此：
UPDATE supplier_dropship_items as t,
(
SELECT id, product_name, sku, qty
FROM supplier_dropship_items
WHERE supplier_id = '3' AND status = '2'
) as temp
SET status = '1' WHERE temp.ID = t.ID;
This is assuming you have an ID column inside your table as this is how it should be set up and how any normalized table would look like.
这是假设您的表中有一个 ID 列，因为这是它的设置方式以及任何规范化表的外观。
