Insert Data if not exists (from 2 tables) and Update otherwise

Question

Good day. I have 3 tables:

tblWarehouseProducts:

 ProductID
 ProductName
 ProductCode
 Quantity

tblBranchProducts:

 ProductID
 ProductCode
 ProductCode
 Quantity
 Location

tblStockMoves:

 ProductID
 DestinationLocation
 Quantity
 ReferenceNumber

Basically, the process is that Branch X requests a product from Warehouse Y. Warehouse Y then creates a request order(called a Stock Move) and stores the request in tblStockMove.

Say for this case, we have a Stock Move with Reference Number XYZ:

REFERENCE NO.  |   PRODUCT ID  |   DESTINATION   |   QTY   |
XYZ            |       1       |     BRANCH Y    |    5    |
XYZ            |       2       |     BRANCH Y    |    6    |

(where ProductID 1 is Coke and ProductID 2 is Pepsi.)
Branch X on the other hand has this product on stock:

PRODUCT ID  |  PRODUCT NAME   | PRODUCT CODE  |   QUANTITY |   LOCATION   |
1           |      COKE       |    ABC123     |      6     |    Branch X  |

I am currently trying to check if the items from tblStockMoves exist in tblBranchProducts.

If Product 1 exists, it will add the Qty from tblStockMoves to the current Qty in tblBranchProducts. Product 2 will be added as a new entry since it is a new item.

I am using this query below but so far, all it does is update the stock of ProductID 1 while ignoring (not inserting) Product ID 2.

IF EXISTS (select ProductID, Location 
           from tblBranchProducts a 
           where Location = 'Branch X' 
             and a.ProductID in (select b.ProductID  
                                 from tblStockMoves b 
                                 where b.ReferenceNumber = 'XYZ' 
                                   and b.DestinationLocation = 'Branch X'))
BEGIN
    UPDATE tblBranchProducts 
    SET Quantity = a.Quantity + b.Quantity
    FROM tblBranchProducts a 
    INNER JOIN tblStockMoves b ON a.ProductID = b.ProductID 
    WHERE 
        b.ReferenceNumber = 'XYZ' 
        AND b.DestinationLocation = 'Branch X'
END
ELSE
BEGIN
    INSERT INTO tblBranchProducts (ProductID, ProductName, ProductCode, Quantity, Location) 
        SELECT 
            b.ProductID, a.ProductName, a.ProductCode, b.Quantity, b.DestinationLocation 
        FROM 
            tblStockMoves b 
        INNER JOIN
            tblWarehouseProducts a ON b.ProductID = a.ProductID 
        WHERE 
            b.ReferenceNumber = 'XYZ' 
            AND b.DestinationLocation = 'Branch X'

Other details such as Product Name and Product Code are pulled from tblWarehouseProducts and then inserted to tblBranchProducts.

Can anyone tell me why my query only updates the existing stock of Product 1 and not inserting Product 2?

Your answers are deeply appreciated!

Answer 1

You can do it dynamically for all products with out IF's , just addthe conditions required:

/*will insert all the unmatched products*/
INSERT INTO tblBranchProducts  (ProductID, ProductName, ProductCode, Quantity, Location) 
SELECT b.ProductID, a.ProductName, a.ProductCode, b.Quantity, b.DestinationLocation
FROM tblStockMoves b
inner join tblWarehouseProducts a on b.ProductID = a.ProductID
LEFT JOIN tblBranchProducts  c ON(a.productid = b.productid)
where  c.productid is null

And:

/*will update all the matching products*/
update tblBranchProducts a
INNER join tblStockMoves b on a.productid = b.productid
set a.quantity= b.qty