Link to home
Start Free TrialLog in
Avatar of peterdevadoss
peterdevadoss

asked on

Simple Sql query

table1: TransactionTable
ProdNo,Qty,Location
10,5,'A1'
20,9,'A2'

table2:ProductTable
ProdNo,Qty,Location
10,20,'A1'
11,34,'A2'
12,42,'A3'

I would like to add the qty in table2 with the qty from table1 with following preferences:
1. if prodNo found in table2 then use update command
2. if prodNo not found in table 2 then use insert command

is there anyone to get me the sql query for this?
Avatar of Brendt Hess
Brendt Hess
Flag of United States of America image

The traditional way is to use a dual query:

UPDATE Table1
SET Qty = t1.Qty + t2.Qty
FROM Table1 t1
INNER JOIN Table2 t2
    ON t2.prod_no = t1.prod_no

INSERT INTO Table1 (ProdNo, Qty, Location)
SELECT t2.ProdNo,
    t2.Qty,
    t2.Location
FROM Table2 t2
LEFT JOIN Table1 t1
    ON t2.ProdNo = t1.ProdNo
WHERE t1.ProdNo is null
Avatar of peterdevadoss
peterdevadoss

ASKER

the update query is ok.
insert query is not working.  here is the modified query:
INSERT INTO productDetail (Prod_sino,loc_sino,loc_zone,Qty)
SELECT t2.Prod_sino,2,'A3',t2.qty
FROM stockTransferDetails t2
INNER JOIN productDetail t1   ON t2.Prod_siNo = t1.Prod_siNo
WHERE t1.Prod_siNo is null

it should return one row, but returns nothing.
ASKER CERTIFIED SOLUTION
Avatar of Brendt Hess
Brendt Hess
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial