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.

Bohemian · Accepted Answer · 2011-07-28T07:16:20.320

79

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

If no rows are returned from the select (because your special condition is false) no insert happens.

Using your schema from question (assuming your id column is auto_increment):

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

This will insert no rows if there's not enough stock on hand, otherwise it will create the order row.

Nice idea btw!

edited Jul 28 '11 at 07:16

answered Jul 28 '11 at 06:46

Bohemian

412,405
93
575
722

@Bohemian: No need for two `SELECT` statements. One will suffice. Take a look at my answer. :) – Shef Jul 28 '11 at 07:20
True, but I was trying to match my *general* pattern. I like your answer though... +1 – Bohemian Jul 28 '11 at 07:37
@Joseph Silber: To do what??? Where is the subtraction there? What does subtraction have to do with normalization? :D Do you understand that the above query is composed of two `SELECT` statements compared to mine, which runs with one? (Nothing against your answer, or you, Bohemian). – Shef Jul 28 '11 at 14:52
@Shef: In your code, after `INSERT`, if `affected rows = 1`, you'll `UPDATE ... qty_on_hand = qty_on_hand - 20`. That's how you keep track of stock. However, the way I do it (see my code examples above) is that `qty_on_hand` never changes. It is always set to the amount of stock in the warehouse, regardless of how many have sold. Then, when I want to check if a product is available, I compare `SUM(qty) FROM orders` to `qty_on_hand FROM products`. This is only possible with a subquery (granted, @Bohemian didn't really do it so either, but we're on the same page as far as requiring a subquery). – Joseph Silber Jul 28 '11 at 16:51
@Joseph: You were talking about DB normalization, now you come back with inconsistent data? Handle the tasks buddy, the overhead of doing the sum at order time is way much more than the overhead of updating the record. ;) – Shef Jul 28 '11 at 16:55
@Shef: Correct me if I'm wrong. You're effectively taking the information available in `orders`, and duplicating it in `products`. You're talking about overhead from the DB's prospective. What about mine? Going at it your way, if an order is cancelled, I have to increase the `qty_on_hand`. There are many other scenarios that might affect the `qty_on_hand`. Again, duplicating information is always a bad idea. `SUM()` is not that expensive (even if there are 5000 orders for this particular product, we're still simply adding numbers; which is the fastest thing for any program to do). – Joseph Silber Jul 28 '11 at 17:07
@Joseph: I am not duplicating anything. I am doing an insert into `orders` for product id `1` with the amount `20` if and only if there is enough quantity at hand to satisfy the order. Anyways, do it your way, since you are all into it. – Shef Jul 28 '11 at 17:10
@Shef: Sorry buddy. Didn't mean to offend you. You really helped me out here. Thanks. – Joseph Silber Jul 28 '11 at 17:12
@Joseph: I am not offended at all. I was just trying to let you understand that the way your app logic is running right now is not the most efficient one. That's why I insisted, but then I saw you don't like to or can't change it, so I just gave up. :) – Shef Jul 28 '11 at 17:32

score 24 · Answer 2 · answered Jul 28 '11 at 07:16

24

Try:

INSERT INTO orders(product_id, qty)
SELECT 2, 20 FROM products WHERE id = 2 AND qty_on_hand >= 20

If a product with id equal to 2 exists and the qty_on_hand is greater or equal to 20 for this product, then an insert will occur with the values product_id = 2, and qty = 20. Otherwise, no insert will occur.

Note: If your product ids are note unique, you might want to add a LIMIT clause at the end of the SELECT statement.

answered Jul 28 '11 at 07:16

Shef

44,808
15
79
90

How would you negate this? – user2340939 Nov 12 '17 at 14:42
1

One way is to add at the end the following: HAVING COUNT(*) = 0. – user2340939 Nov 12 '17 at 14:51

score 2 · Answer 3 · answered Jul 28 '11 at 06:58

Not sure about concurrency, you'll need to read up on locking in mysql, but this will let you be sure that you only take 20 items if 20 items are available:

update products 
set qty_on_hand = qty_on_hand - 20 
where qty_on_hand >= 20
and id=2

You can then check how many rows were affected. If none were affected, you did not have enough stock. If 1 row was affected, you have effectively consumed the stock.

score 1 · Answer 4 · answered Jul 28 '11 at 06:52

1

You're probably solving the problem the wrong way.

If you're afraid two read-operations will occur at the same time and thus one will work with stale data, the solution is to use locks or transactions.

Have the query do this:

lock table for read
read table
update table
release lock

answered Jul 28 '11 at 06:52

Konerak

39,272
12
98
118

1

I'm not so sure locking is the best solution. It might cause serious performance issues. – Joseph Silber Jul 28 '11 at 07:04

score 0 · Answer 5 · answered May 14 '20 at 18:41

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 Answers5

Linked