0

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;
abhi
  • 1,760
  • 1
  • 24
  • 40
Lawrence
  • 21
  • 1
  • 1
  • if you craft your unique keys (including composite keys ie multi-column keys) properly, you can get away with `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 – Drew Oct 12 '16 at 19:27

2 Answers2

2

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

Matthias Winkelmann
  • 15,870
  • 7
  • 64
  • 76
  • This sent me in the right direction. I used this (mysql/MariaDB): `IINSERT into tbl (col1, col2) SELECT 'val1', 'val2' FROM table WHERE (col1 = 'val1' AND col2 = 'val2') HAVING COUNT(*) = 0;` inspired also by [this answer](https://stackoverflow.com/a/7780786/1879728) – vlz May 03 '21 at 14:04
0

Not 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.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • well i might have explained it wrong. I want to INSERT INTO sales_items (sale_id, item_id, quantity_purchased, item_cost_price, item_unit_price) VALUES ('1', '1546', '3', '10', '10') – Lawrence Oct 12 '16 at 19:35
  • only if (SELECT quantity FROM location_items WHERE location_id =4 AND item_id =1546) < 10) Its 2 different tables. I dont need to pull information to write to the other – Lawrence Oct 12 '16 at 19:36
  • the `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". – Marc B Oct 12 '16 at 19:43