使用三个表的股票合并语句 [英] Merge statement for stocks using three tables

问题描述

这是 SQL 查询:

MERGE tblProductsSold在 tblOrders.OrderID = tblProductsSold.txtOrderID 上使用 tblOrders当不匹配时插入(txtOrderID、txtOrderdate、txtPartno、txtQty)价值观(选择 tblItemsOnOrder.txtOrderID，tblOrders.txtDateTime,tblItemsOnOrder.txtPartNO,tblItemsOnOrder.txtQTY从 tblOrders INNER JOIN tblItemsOnOrderON tblOrders.OrderID = tblItemsOnOrder.txtOrderID哪里 tblOrders.txtIsConfirmed = '1')输出$动作;

期望的结果:需要导入Products的订单，这些订单不在tblProductsSold表中

解决方案

你不能像现在那样接近它.

MERGE 语句合并两个表 - 您在标题中定义的两个表 - 源 表和 目标 表.>

现在，您使用 tblOrders 作为源，tblProducts 作为目标.仅此一项似乎很奇怪 - 您正在尝试将订单合并到产品中?好像不太合适……

一旦您定义了源表和目标表 - 您就可以比较源表中的哪些行存在于(或不存在)中.如果源中的给定行不存在于目标中 - 那么您可以将其值插入目标表中.

但这仅适用于源表中的直接列值！你不能像你试图做的那样出去对其他表进行子查询！

所以我相信你真正应该做的是:

• 作为您的来源 - 有一个视图列出在您的订单中找到的产品 - 产品(不是订单本身)

• 然后将您的 Products 表与此视图进行比较 - 如果您的订单碰巧包含基本 Products 表中不存在的任何产品 - 插入它们.

所以你需要这样的东西:

MERGE tblProductsSold AS Target使用 (SELECT tblItemsOnOrder.txtOrderID, tblOrders.txtDateTime,tblItemsOnOrder.txtPartNO, tblItemsOnOrder.txtQty来自 tblOrders内部连接 ​​tblItemsOnOrder ON tblOrders.OrderID = tblItemsOnOrder.txtOrderIDWHERE tblOrders.txtIsConfirmed = '1') 作为源ON Source.OrderID = Target.txtOrderID当不匹配时插入(txtOrderID、txtOrderdate、txtPartno、txtQty)值(Source.OrderID、Source.txtDateTime、Source.txtPartNo、Source.txtQty)输出$动作;

Here is the SQL Query:

MERGE  tblProductsSold

USING tblOrders on tblOrders.OrderID = tblProductsSold.txtOrderID

WHEN NOT MATCHED THEN 

Insert ( txtOrderID, txtOrderdate, txtPartno, txtQty)  
values 
(SELECT tblItemsOnOrder.txtOrderID, 
 tblOrders.txtDateTime, 
 tblItemsOnOrder.txtPartNO, 
 tblItemsOnOrder.txtQTY
FROM tblOrders  INNER JOIN tblItemsOnOrder 
ON tblOrders.OrderID = tblItemsOnOrder.txtOrderID
WHERE tblOrders.txtIsConfirmed = '1'
)

OUTPUT $action ;

Desired Result: need to import orders with Products that are not already in the tblProductsSold table

解决方案

You cannot approach it like you are doing it right now.

The MERGE statement merges two tables - the two tables you define in the header - the source table and the target table.

Right now, you're using tblOrders as your source, and tblProducts as your target. That alone seems odd - you're trying to merge orders into products? Doesn't seem very fitting...

Once you've defined your source and target table - you stat comparing which rows from the source are present in the target (or not). If a given row from your source is not present in the target - then you can insert its values into the target table.

But that only works for direct column values from the source table! You cannot go out and do subqueries into other tables as you're trying to do!

So I believe what you really should do is this:

• as your source - have a view that lists the products found in your orders - the products (not the orders per se)

• then compare your Products table to this view - if your orders happen to have any products that aren't present in the base Products table - insert them.

So you'd need something like:

MERGE  tblProductsSold AS Target
USING (SELECT tblItemsOnOrder.txtOrderID, tblOrders.txtDateTime, 
              tblItemsOnOrder.txtPartNO, tblItemsOnOrder.txtQty
       FROM tblOrders  
       INNER JOIN tblItemsOnOrder ON tblOrders.OrderID = tblItemsOnOrder.txtOrderID
       WHERE tblOrders.txtIsConfirmed = '1') AS Source
   ON Source.OrderID = Target.txtOrderID

WHEN NOT MATCHED THEN 
    INSERT (txtOrderID, txtOrderdate, txtPartno, txtQty)  
    VALUES (Source.OrderID, Source.txtDateTime, Source.txtPartNo, Source.txtQty)

OUTPUT $action ;

这篇关于使用三个表的股票合并语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！