MySQL - SELECT then UPDATE
mysql, select, sql-update
Solution
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>;
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.
Problem
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); ``` 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); } ```