MySQL INSERT IF (custom if statements)

Question

First, here's the concise summary of the question:

Is it possible to run an INSERT statement conditionally? Something akin to this:

IF(expression) INSERT...

Now, I know I can do this with a stored procedure. My question is: can I do this in my query?

Now, why would I want to do that?

Let's assume we have the following 2 tables:

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

Now, let's say an order for 20 Voodoo Dolls (product id 2) comes in.
We first check if there's enough 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');

Then, if it evaluates to true, we run an INSERT query.
So far so good.

However, there's a problem with concurrency.
If 2 orders come in at the exact same time, they might both read the quantity-on-hand before any one of them has entered the order. They'll then both place the order, thus exceeding the qty_on_hand.

So, back to the root of the question:
Is it possible to run an INSERT statement conditionally, so that we can combine both these queries into one?

I searched around a lot, and the only type of conditional INSERT statement that I could find was ON DUPLICATE KEY, which obviously does not apply here.

user_9811343 · Answer

I wanted to insert into a table using values so I found this solution to insert the values using the IF condition

DELIMITER $$

CREATE PROCEDURE insertIssue()
BEGIN
    IF (1 NOT IN (select I.issue_number from issue as I where I.series_id = 1)) THEN
        INSERT IGNORE INTO issue ( issue_number, month_published, year_published, series_id, mcs_issue_id) VALUES (1, 1, 1990, 1, 1);
    END IF;
END$$

DELIMITER ;

If you later on want to call the procedure it's as simple as

CALL insertIssue()

You can find more information about PROCEDURES and if conditions in this site

MySQL INSERT IF (custom if statements)

5 Answers