18

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); 
}
TheWebsiteGuy
  • 304
  • 1
  • 2
  • 12
  • 2
    run update query after select query – M Khalid Junaid Jul 05 '14 at 20:25
  • OK, Sorry i'm learning MySQL. If I did a seperate update after the select I could technically change a product that may have been added after the select that would not be then available to select the next time the script runs? If that makes sense. – TheWebsiteGuy Jul 05 '14 at 20:29
  • Not if you update only the SKUs that you have selected in the first place. (Or cue on whatever else is your primary key) – ESG Jul 05 '14 at 20:30
  • do you have any primary key defined in table if so then you can run `update table set status = '1' where primarykey_column in(primaykey ids of selected records)` – M Khalid Junaid Jul 05 '14 at 20:35

2 Answers2

24

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

John Ruddell
  • 25,283
  • 6
  • 57
  • 86
  • 1
    Hi @John Ruddell, I have just tested this and although it did update the item's selected they where not passed to `$result` for me to then use in my php script? – TheWebsiteGuy Jul 05 '14 at 20:38
  • ok then just run the query before you run the update. basically just run your query so you have it in $result and then update the table afterwards if that makes sense. – John Ruddell Jul 05 '14 at 20:40
  • 4
    This syntax results in errors on my MySQL server: 1. An expression was expected. (near "(" at position 39) 2. Unexpected token. (near "(" at position 39) 3. A new statement was found, but no delimiter between it and the previous one. (near "SELECT" at position 46) – Sam Tuke Mar 24 '16 at 16:07
  • @samtuke it's valid syntax, did you not copy over how to do it correctly? – John Ruddell Mar 24 '16 at 16:38
  • 1
    @JohnRuddell As mentioned above, your solution above does not return the values of product_name, sku, qty to $result. And running the select before the update I think breaks the purpose of the code block, which is "locking" the record by updating and selecting in 1 command, so a 2nd transaction cannot grab/update the record. So my question is this.. Is it possible to push the data you store in temp to $result within this one command? – Wyatt Jackson Nov 11 '21 at 01:47
  • @WyattJackson two things, this is a fairly old post with an already accepted answer.. does that one not help? The other thing is this sql query I have provided here is valid in syntax and does run fine, you can see an [example here on sqlfiddle](http://sqlfiddle.com/#!9/d752f22/3). It sounds like the issue is whatever php package you're using doesn't support some form of this syntax? not really sure what it would be really off the top of my head. – John Ruddell Nov 11 '21 at 07:27
  • @WyattJackson I updated the answer to include more details of what the sql update statement is doing and added a link to the documentation for it. Hope that's helpful :) – John Ruddell Nov 11 '21 at 07:50
  • @JohnRuddell Thank you for your help. I'm trying to do this: $result = "Your solution above", and have $result include the values of id, product_name, sku, qty. As mentioned by TheWebsiteGuy above. – Wyatt Jackson Nov 12 '21 at 05:46
  • @WyattJackson an update statement doesn't return a result. You need to run the queries separately (update, select). The select statement I put in the update statement is just for the purposes of the update and is still just a part of the update statement.. the use case for this syntax is for complicated logic needed that a regular simpler update statement without a select. So what you're asking for isn't really possible. – John Ruddell Nov 12 '21 at 05:57
6

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.

Mariano D'Ascanio
  • 1,202
  • 2
  • 16
  • 17
  • 1
    you can also just lock the tables so the order wont cause a deadlock (in InnoDB). `LOCK TABLES supplier_dropship_items WRITE` – John Ruddell Jul 05 '14 at 20:35
  • Thanks @Marion C'Ascanio, this works perfectly and more importantly I understand it :) I will add the result to the question for reference. – TheWebsiteGuy Jul 05 '14 at 20:50