MySQL INSERT 語句可以有條件嗎？

Conditional INSERT Statements in MySQL

Question: Is it feasible to perform an INSERT operation conditionally, akin to an IF statement?

Elaboration:

Suppose we need to place an order for 20 items with product ID 2. To ensure sufficient stock, we first check the quantity on hand:

SELECT IF(
    ( SELECT SUM(qty) FROM orders WHERE product_id = 2  ) + 20
    <=
    ( SELECT qty_on_hand FROM products WHERE id = 2)
, 'true', 'false');

If the result is true, we execute the INSERT query. However, this approach is susceptible to concurrency issues. Multiple simultaneous orders can result in overselling.

Answer:

Yes, conditional INSERT statements are possible using the following syntax:

INSERT INTO TABLE
SELECT value_for_column1, value_for_column2, ...
FROM wherever
WHERE your_special_condition

If the SELECT statement returns no rows (i.e., the special condition is false), no insertion occurs.

Example:

For the given schema:

products: id, qty_on_hand
orders: id, product_id, qty

insert into orders (product_id, qty)
select 2, 20
where (SELECT qty_on_hand FROM products WHERE id = 2) > 20;

This query will only insert a row if there is sufficient stock on hand. Otherwise, it will not execute the insertion.

以上是MySQL INSERT 語句可以有條件嗎？的詳細內容。更多資訊請關注PHP中文網其他相關文章！