MySQL Update Multiple Rows in one table

Question

+-----------+---------------+-------------+---------------------+
| ProductID | Description   | CostPerItem | NumberOfItemsOnHand |
+-----------+---------------+-------------+---------------------+
| 11        | Bike          |         150 |                 200 |
| 12        | Socks         |           2 |                 100 |
| 55        | Bicycle       |         150 |                 120 |
| 66        | Elbow pads    |          14 |                 120 |
| 78        | Knee Pads     |          12 |                  70 |
| 88        | Roller Blades |          75 |                  89 |
| 99        | Helmet        |          29 |                  30 |
+-----------+---------------+-------------+---------------------+

+------------+-----------+------------+-------------+-------------+---------+
| CustomerID | FirstName | LastName   | City        | PhoneNumber | AgentID |
+------------+-----------+------------+-------------+-------------+---------+
| 10         | Barney    | Rubble     | Bedrock     |        -457 | NULL    |
| 12         | Monty     | Burns      | Springfield |         789 | NULL    |
| 13         | Wonder    | Woman      | Kypto       |        -346 | NULL    |
| 14         | Peter     | Griffens   | Providence  |       -3580 | NULL    |
| 15         | Fred      | Flintstone | Bedrock     |       -1568 | NULL    |
| 21         | Homer     | Simpson    | Springfield |       -8270 | NULL    |
| 31         | Sideshow  | Bob        | Springfield |       -4869 | NULL    |
+------------+-----------+------------+-------------+-------------+---------+

+----------+-----------+------------+
| Quantity | ProductID | CustomerID |
+----------+-----------+------------+
|       20 | 99        | 21         |
|       14 | 12        | 21         |
|       10 | 66        | 21         |
|        1 | 99        | 31         |
|        2 | 12        | 31         |
|        4 | 78        | 31         |
|        2 | 66        | 31         |
|        2 | 66        | 15         |
|        2 | 78        | 15         |
|       19 | 66        | 14         |
+----------+-----------+------------+

I have 3 tables, one containing store products, one containing customer list and one table drawing relationship between the products bought and by which customer by holding the quantity, product ID and customer ID, which are both keys in their respective tables

I want to do write a statement where I can update the value of NumberOfItemsOnHand in the product table to 20 for each product thats been bought by "Sideshow" (first name in the customer table)

I tried to use write the statement:

UPDATE Product x
   SET x.NumberOfItemsOnHand = 20 
 WHERE x.ProductID = 
    (SELECT ProductID from BoughtBy WHERE BoughtBy.CustomerID = 
       (SELECT CustomerID FROM Customer WHERE FirstName = "Sideshow")
    );

I get an error saying "Subquery returns more than 1 row" and I have no idea on how to do it another way

Madhur Bhaiya · Accepted Answer · 2018-11-01 14:05:11Z

1

You can approach this problem using Inner Join more efficiently instead.
Join all the tables using their appropriate relationships and consider only those rows where customer.FirstName = "Sideshow", and update the corresponding product's NumberOfItemsOnHand value.

Try the following query instead:

UPDATE Product AS p
JOIN BoughtBy AS b 
  ON b.ProductId = p.productID 
JOIN Customer AS c 
  ON c.CustomerID = b.CustomerID AND 
     c.FirstName = "Sideshow"
SET p.NumberOfItemsOnHand = 20

edited Nov 1, 2018 at 14:05

answered Nov 1, 2018 at 14:02

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Max · Accepted Answer · 2018-11-01 14:05:42Z

0

Have you tried:

UPDATE Product x
   SET x.NumberOfItemsOnHand = 20 
 WHERE x.ProductID IN 
    (SELECT ProductID from BoughtBy WHERE BoughtBy.CustomerID IN
       (SELECT CustomerID FROM Customer WHERE FirstName = "Sideshow")
    );

answered Nov 1, 2018 at 14:05

Max

2,5911 gold badge18 silver badges29 bronze badges

Collectives™ on Stack Overflow

MySQL Update Multiple Rows in one table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related