MySQL INSERT IF (custom if statements)

Solution 1:

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!

Solution 2:

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.

Solution 3:

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.

Solution 4:

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