Update a table with conditions in specfic order

Question

I have searched the web for my problem, tested some subqueries and derived table approaches with Case Statements, but didn´t get the result. Perhaps you can help? Thanks.

The examples below are just an example.

# generate the table as it is 
DROP TABLE IF EXISTS `IN`;
CREATE TABLE `IN`
(`Part` CHAR(1),
`Warehouse` INT(1), 
`Percentage` INT(1),
`Update` INT(1));

#some values for the table
INSERT INTO `IN`
(Part, Warehouse, Percentage)
VALUES 
  ('A' , 1, 80),
  ('A',  2, 100),
  ('A',  3, 50),
  ('B',  1, 100),
  ('B',  2, 50),
  ('B',  3, 100);

 # generate table as it should be
 DROP TABLE IF EXISTS `OUT`;
 CREATE TABLE `OUT`
 (`Part` CHAR(1),
  `Warehouse` INT(1),
  `Percentage` INT(1),
  `Update` INT(1));

 # values for the table
 INSERT INTO `OUT`
 (Part, Warehouse, Percentage, `Update`)
  VALUES 
  ('A' , 1, 80, 2),
  ('A',  2, 100, 2),
  ('A',  3, 50, 2),
  ('B',  1, 100, 3),
  ('B',  2, 50, 3),
  ('B',  3, 100, 3);

I would like to add the specific warehouse name in the column Update for the specific part if the percentage is 100.

The value of the warehouse should be filled to every row for the specific part.

The fill calculation of the update column should be in a specific order. So first there should be a check if warehouse 3 has 100 and take this value. If warehouse 3 only has 50 then check warehouse 2, if it has 100.

Thank you very much!

Answer 1

Here's one way...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(Part CHAR(1)
,Warehouse INT NOT NULL
,Percentage TINYINT NOT NULL
);

INSERT INTO my_table
(Part, Warehouse, Percentage)
VALUES 
  ('A' , 1, 80),
  ('A',  2, 100),
  ('A',  3, 50),
  ('B',  1, 100),
  ('B',  2, 50),
  ('B',  3, 100);

SELECT w1.*, COALESCE(w3.warehouse,w2.warehouse,w1.warehouse) warehouse 
  FROM my_table w1 
  LEFT 
  JOIN my_table w2
    ON w2.part = w1.part 
   AND w2.warehouse = 2 
   AND w2.percentage = 100
  LEFT 
  JOIN my_table w3
    ON w3.part = w1.part 
   AND w3.warehouse = 3 
   AND w3.percentage = 100;

+------+-----------+------------+-----------+
| Part | Warehouse | Percentage | warehouse |
+------+-----------+------------+-----------+
| A    |         1 |         80 |         2 |
| A    |         2 |        100 |         2 |
| A    |         3 |         50 |         2 |
| B    |         1 |        100 |         3 |
| B    |         2 |         50 |         3 |
| B    |         3 |        100 |         3 |
+------+-----------+------------+-----------+