I am trying to insert into a table only if the qty has changed down in another table see example
INSERT INTO sales_items (sale_id, item_id, quantity_purchased, item_cost_price, item_unit_price)
VALUES ('1', '1546', '3', '10', '10')
WHEN (SELECT quantity FROM location_items WHERE location_id =4 AND item_id =1546) < 10;
You can do the following:
INSERT INTO sales_items
(sale_id, item_id, quantity_purchased, item_cost_price, item_unit_price)
VALUES
(SELECT '1', '1546', '3', '10', '10'
FROM location_items
WHERE location_id = 4
AND item_id = 1546
AND quantity < 10
);
Or, if you want to do it all in one query, including updates:
REPLACE INTO sales_items
(item_id, quantity_purchased, item_cost_price, item_unit_price)
VALUES
(SELECT item_id, ??, ??, ??
FROM location_items
WHERE quantity < 10
AND quantity > 0
);
...where you have to fill the ?? with references to columns holding the values for item_cost_price and item_unit_price, and you have a unique constraint on item_id
IINSERT into tbl (col1, col2) SELECT 'val1', 'val2' FROM table WHERE (col1 = 'val1' AND col2 = 'val2') HAVING COUNT(*) = 0; inspired also by this answerNot possible like that. An INSERT query cannot have a where clause, period.
You can, hover, do an insert select from:
INSERT INTO ...
SELECT ... FROM ... WHERE (...) < 10
If the SELECT finds no rows, then nothing gets inserted.
select portion can be ANY kind of select query, with joins, other tables, blah blah blah blah. the only real difference is that any found rows will be inserted into your specified table, instead of "returned".
insert ignore, do what Marc says below (with insert ignore), and not worry about duping up your data. It depends on what you are doing which is not at all clear