mysql insert if condition true

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.

Now, I want to do that

Let's assume we have the following 3 tables:

products: id, qty_on_hand
orders: id, product_id, qty
roomTable :id,product_id,room_number,booked_status

Now, let's say an order for 20 rooms (product id 2) comes in. We first check total qty of given product_id from orders table as total_qty and total_qty < qty_on_hand if it return true then insert order values and update 1 room after last booked_status='Y' room from roomTable with booked_status ='Y' and give the id of that room .

Problem is that I am not able to make the join query . what i done so far -

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

Answer 1

For those of you who need more detail around how this technique works from Mt. Schneiders,

INSERT INTO orders(product_id, qty)
SELECT 2, 20 
  FROM products 
 WHERE id = 2 AND qty_on_hand >= 20 + (select sum(qty) from orders where product_id = 2)

What is going on here is that the result of the select statement is hard coded. In this case we are taking INSERT INTO orders(product_id, qty) VALUES(2, 20) and replacing VALUES(2,20) with SELECT 2, 20. The result of the SELECT is hard coded but only returns a value if the condition is met.

I found this article that explains this technique more in depth:

http://boulderapps.co/dont-insert-when-maximum-is-reached

Answer 2

Wouldn't it be something like this?

INSERT INTO orders(product_id, qty)
SELECT 2, 20 
  FROM products 
 WHERE id = 2 AND qty_on_hand >= 20 + (select sum(qty) from orders where product_id = 2)

So, it would not insert if the last orders quantity plus quantity from the order being inserted is less or equal whats in hand.