如果不存在则插入数据(来自2个表)，否则更新 [英] Insert Data if not exists (from 2 tables) and Update otherwise

问题描述

美好的一天.我有3张桌子:

Good day. I have 3 tables:

tblWarehouseProducts :

 ProductID
 ProductName
 ProductCode
 Quantity

tblBranchProducts :

 ProductID
 ProductCode
 ProductCode
 Quantity
 Location

tblStockMoves :

 ProductID
 DestinationLocation
 Quantity
 ReferenceNumber

基本上，该过程是分支X向仓库Y请求产品.然后仓库Y创建一个请求订单(称为库存移动)并将请求存储在 tblStockMove .

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.

在这种情况下，我们有一个参考编号为XYZ的库存移动:​​

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    |

(其中ProductID 1是可乐，ProductID 2是百事可乐.)
另一方面，分支X上有此产品:

(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  |

我当前正在尝试检查tblBranchProducts中是否存在tblStockMoves中的项目.

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

如果存在产品1，它将把tblStockMoves中的数量添加到tblBranchProducts中的当前数量.由于产品2是新商品，因此将其添加为新条目.

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.

我在下面使用此查询，但到目前为止，它所做的只是更新ProductID 1的库存，而忽略(而不是插入)Product ID 2.

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'

其他详细信息(例如产品名称和产品代码)从tblWarehouseProducts中提取，然后插入到tblBranchProducts中.

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

谁能告诉我为什么我的查询仅更新产品1的现有库存而没有插入产品2?

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

您的答案深表感谢！

推荐答案

您可以为所有不带IF的产品动态地做到这一点，只需添加所需条件即可:

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

并且:

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

这篇关于如果不存在则插入数据(来自2个表)，否则更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！