MySQL - SELECT, then UPDATE

Question

I have a script written in PHP that has this line that works correctly to select 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); 

What I'm struggling with is a way to update the records I have selected. After selecting, I need to change status = '1' so that the next time my script runs it, it will not pull out the same data in select and pull out only new items in the table that have status 2.

This is my working result thanks to the comments of the 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); } 

Answer 1

If supplier_dropship_items has a primary key (it should), then include these fields in SELECT , then when you view the results, do UPDATE using the primary key to set status , as in:

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

It is assumed that you are not running a parallel environment. If so, then you must block the records for updating using SELECT... FOR UPDATE . You can read about it here . As far as I know, this works in MySQL on InnoDB tables.

Answer 2

just do the update when you select it at the same time.

change it ...

 SELECT product_name, sku, qty FROM supplier_dropship_items WHERE supplier_id = '3' AND status = '2' 

:

 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 

It is assumed that you have an identifier column inside your table, as it needs to be configured and how any normalized table will look like