MySQL - 选择然后更新 [英] MySQL - SELECT then UPDATE

问题描述

我有一个用 PHP 编写的脚本，其中的这一行可以正常工作以选择我需要的数据；

I have a script written in PHP which has this line which is working correctly for selecting the data i need;

$result = mysql_query("SELECT product_name, sku, qty FROM supplier_dropship_items WHERE supplier_id = '3' AND status = '2'", $db_beb);

我正在努力更新我选择的记录的方法，一旦选择我需要更改 status = '1' 以便下次我的脚本运行时它不会't 在 select 中拉取相同的数据，只会拉取表中状态为 2 的新项目.

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.

感谢下面接受的答案的评论，这是我的工作结果；

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); 
} 

推荐答案

如果 supplier_dropship_items 有一个主键(应该)，那么将这些字段包含在 SELECT，然后，当您循环查看结果时，使用主键执行 UPDATE 以设置 status，如在:

If supplier_dropship_items has a primary key (it should), then include those fields in the SELECT, then, when you cycle through the results, execute an UPDATE using the primary key to set the status, as in:

UPDATE supplier_dropship_items SET status=1 WHERE <id_field>=<id_value>;

这假设您没有在并发环境中执行.如果是，则应使用 SELECT... FOR UPDATE 锁定记录以进行更新.您可以在这里阅读.据我所知，这适用于 InnoDB 表上的 MySQL.

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.

这篇关于MySQL - 选择然后更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！