CODEWITHSUNDEEP
mysql
Hamza Bana
Hamza Bana

Reputation: 39

MySQL Update Multiple Rows in one table

+-----------+---------------+-------------+---------------------+
| 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

Upvotes: 1

Views: 26

Answers (2)

Max
Max

Reputation: 2581

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

Upvotes: 0

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28864

  • 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

Upvotes: 1

Related Questions