MySQL子查询返回多行错误解决及库存更新SQL编写求助

Hey there! Let's tackle your two MySQL challenges one by one—first resolving that annoying Subquery returns more than 1 row error, then crafting the correct SQL to update inventory when food is prepared.

First: Why the Error Happens (and How to Fix It) #

This error pops up when you use a subquery that returns multiple rows in a context where MySQL expects only a single value. For example:

• Using = instead of IN when comparing against a subquery result
• Forgetting to add a filter that narrows the subquery to one row
• Using a subquery that inherently returns multiple rows (like fetching all ingredients for a food) in a single-value spot (like setting a column value)

Here are common fixes:

• Replace = with IN: If your subquery is meant to match multiple values (e.g., finding all material IDs linked to a food), use IN instead of =. Example:

  -- Wrong (throws error if food has multiple ingredients)
  SELECT * FROM tb_stock WHERE material_id = (SELECT material_id FROM tb_ingredients WHERE food_id = 5);
  
  -- Correct
  SELECT * FROM tb_stock WHERE material_id IN (SELECT material_id FROM tb_ingredients WHERE food_id = 5);
  

• Use aggregation functions: If you only need a single value from a subquery (e.g., the maximum quantity of an ingredient), wrap it in MAX(), MIN(), or SUM() to force a single result.
• Switch to EXISTS: When checking for existence instead of fetching values, EXISTS is more efficient and avoids this error. Example:

  SELECT * FROM tb_food f
  WHERE EXISTS (SELECT 1 FROM tb_ingredients i WHERE i.food_id = f.food_id);
  

• Validate subquery logic: Double-check if your subquery is missing a WHERE clause or GROUP BY that would limit results to one row.

Second: SQL to Update Inventory When Preparing Food #

Based on your table structure, here's a robust way to update tb_stock by deducting the required raw materials when food is made (using order items as the trigger). This uses joins instead of risky subqueries to avoid the "returns more than 1 row" error.

Assumptions (adjust if your schema differs): #

• tb_ingredients has a quantity column (amount of raw material needed per unit of food)
• tb_order_items has a quantity column (number of food units being prepared)
• tb_stock has a stock_quantity column (current available stock of the raw material)

The Update Query: #

-- Start a transaction to ensure atomicity (critical for inventory operations)
START TRANSACTION;

UPDATE tb_stock s
JOIN (
    -- Calculate total raw material needed for all food items in the order
    SELECT
        i.material_id,
        SUM(i.quantity * oi.quantity) AS total_deduction
    FROM tb_order_items oi
    JOIN tb_ingredients i ON oi.food_id = i.food_id
    -- Optional: Filter to only process specific orders (e.g., confirmed orders)
    -- WHERE oi.order_id = 1234 AND oi.status = 'confirmed'
    GROUP BY i.material_id
) AS required_materials ON s.material_id = required_materials.material_id
-- Deduct the required quantity from stock
SET s.stock_quantity = s.stock_quantity - required_materials.total_deduction
-- Optional: Prevent negative stock (remove if your business allows backorders)
WHERE s.stock_quantity >= required_materials.total_deduction;

-- Commit the transaction if no errors occur; rollback if something goes wrong
COMMIT;

How This Works: #

1. Subquery (required_materials): Joins tb_order_items and tb_ingredients to calculate how much of each raw material is needed total (per-food ingredient quantity × number of food units being made).
2. Join with tb_stock: Links the calculated deductions to the corresponding stock entries.
3. Update Stock: Subtracts the total required quantity from each raw material's stock.
4. Transaction: Ensures that if any part of the update fails, all changes are rolled back to avoid inconsistent inventory.

Additional Best Practices: #

• Add Error Handling: Use ROLLBACK if the update affects fewer rows than expected, or if stock goes negative (if you removed the WHERE clause).
• Lock Rows: For high-concurrency environments, use SELECT ... FOR UPDATE on the stock rows before updating to prevent race conditions.
• Log Changes: Consider adding an audit table to track inventory deductions (e.g., tb_stock_log with material_id, deducted_quantity, timestamp, order_id).

内容的提问来源于stack exchange，提问作者Veerasingam Rajeevan