Search code examples
mysqljoingroup-bymin

MYSQL Updating single matching row instead of all

I am working with mysql and hit a wall. Please see my db-fiddle: https://www.db-fiddle.com/f/nhY88U8gtunUqRaSBLBTrT/0

item_storage is a static table. It is created once and then just updated occasionally.

operation is a dynamic table that gets created when an operation is started and then cleared once the operation is finished.

Every row in operation has unique ID value. For example, in the operation table , I have just 2 rows, both have unique serial numbers. I need to update my item_storage table according to the operation table.

Since in my operation table I have 1 item with serial "AAAAAA" and 1 item with "BBBBBB", I must update my item_storage and set the current_operation and ID to be the same as in the operation table.

The issue is, inside my item_storage, I can have multiple items with matching serial numbers. The only difference they have, is different Slave ID. I want to randomly choose (Or select the one with lower Slave ID ) and update only one matching serial item instead of updating all of them.

As you can see in my sql fiddle , both of the items with Serial "AAAAAA" gets updated in my item_storage table.

Solution

  • With this query:

    SELECT Serial, MIN(Slave_ID) Slave_ID 
FROM item_storage  
GROUP BY Serial

    you get the min Slave_ID for each Serial in item_storage.

    Join item_storage to the above query and also to operation:

    UPDATE item_storage i
INNER JOIN (
  SELECT Serial, MIN(Slave_ID) Slave_ID 
  FROM item_storage  
  GROUP BY Serial
) s ON s.Slave_ID = i.Slave_ID 
INNER JOIN operation o ON o.Serial = i.Serial
SET i.Current_operation = o.Quantity, 
    i.ID = o.ID 
WHERE o.Statusv <> 'DONE';

    See the demo.