Can MySQL INSERT Statements Be Made Conditional?

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.

The above is the detailed content of Can MySQL INSERT Statements Be Made Conditional?. For more information, please follow other related articles on the PHP Chinese website!