MySQL - SELECT then UPDATE

Question

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

Answer 1

Just do the UPDATE when you SELECT it at the same time.

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.

---

Edit

Here is a link for the documentation on this syntax

Essentially what this is doing is while trying to update the table that we here are aliasing as t, you simultaneously run a select statement.
This select statement is returning a result table that we alias with the name temp.
So now imagine the result of your select statement is inside temp, while the whole table you are updating is inside t.
Finally you update the status field to 1 where the ID's (on these two alias result sets) match