-1

i have two tables:

stocklocations:(StockID, Quantity, SLNID)
StockDetail:(StockID, Quantity, SLNID, Type)

i want to insert a query to db to update all Quantity of stocklocations when Type ='B' and StockID are equal with SLNID equal and if SLNID not equal then insert StockID, Quantity, SLNID into stocklocation from stockdetail is this possible?

Hemin
  • 23
  • 9

2 Answers2

0

Use INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO stocklocations (Quantity, SLNID) VALUES( '$quantity', SLNID) where TYPE='B' and StockId = SLNID and  ON DUPLICATE KEY  UPDATE Quantity=VALUES(Quantity+'$quantity');
Naruto
  • 4,221
  • 1
  • 21
  • 32
0

If I got your question correct, if stocklocations contains the row stockID, SLNID then you would like to update the quantity, and if not onsert the row.

If this is the scenario, then you can use the following (assuming you define your key as (StockID, SLNID))

ON DUPLICATE KEY UPDATE `quantity`=`quantity`+1;

So the full query will look like:

INSERT INTO `stocklocations` (`StockID`, `SLNID`, `Quantity`) 
VALUES (1, 1, 1)
ON DUPLICATE KEY UPDATE `quantity`=`quantity`+VALUES(`Quantity`);

Hope it will help,
Liron

Liron
  • 515
  • 4
  • 20
  • what dose VALUES (1, 1, 1) means ? should i get from input? if yes , well i dont want to do this i want to do it for all in the database – Hemin Dec 19 '15 at 21:44
  • VALUES (1, 1, 1) means your values to insert. Looking at your comment, I understand that the main table already exists, and you want to populate stocklocations. If so, I suggest to update the question, and you can do a SELECT-INSERT like in the following example: [link](http://stackoverflow.com/a/2472315/3839692) – Liron Dec 20 '15 at 04:35