使用相关子查询更新查询 [英] Update Query with Correlated Subquery
问题描述
我正在尝试将 Foxpro 应用程序转换为 .NET.作为转换的一部分,我将数据从 DBF 表转换为 Sql 服务器.
I'm trying to convert a Foxpro application into .NET. As part of that conversion I'm converting the data from DBF tables to Sql server.
我需要根据 Orders 表、FirstOrder 和 LastOrder 在 Customer 表中添加几个新字段.
I need to come up with a couple new fields in the Customer table based on the Orders table, FirstOrder and LastOrder.
我似乎无法理解如何在 TSql 中执行此操作.我知道如何在 Foxpro 中执行此操作,如果必须的话,我实际上仍然可以在那里执行此操作,但我知道我需要学习如何在 Sql 中执行此操作.
I just can't seem to muddle through how to do this in TSql. I know how I'd do it in Foxpro, and I could actually still do it there if I had to, but I know I need to learn how to do this in Sql.
这是基本结构.客户表有一个 Id,然后我需要更新 FirstOrder 和 LastOrder 字段.订单表有订单日期,但这里是真正的曲线.客户 ID 可以存在于订单内的 5 个不同字段中:ShipperId、PickupId、ConsigneeId、DeliveryId 或 BillingId.
Here is the basic structure. Customer Table has an Id, then the FirstOrder and LastOrder fields I need updated. Order Table has OrderDate, but here is the real curve. The Customer Id can exist in 5 different fields inside the Order: ShipperId, PickupId, ConsigneeId, DeliveryId, or BillingId.
比如:
UPDATE customers
SET FirstOrderDate =
(Select MIN(OrderDate)
FROM Orders o
WHERE o.ShipperId = Customers.Id or
o.PickupId = Customers.Id or
o.ConsigneeId = Customers.Id or
o.DeliveryId = Customers.Id or
o.BillingId = Customers.Id)
似乎无法找出如何将子查询与主更新查询联系起来.
Just can't seem to find out how to tie the subquery with the main update query.
谢谢,-Sid
这是根据 MarkD 的建议工作的 SELECT:
Here's the SELECT that's working based on MarkD's suggestion:
Select C.Id,Min(o.OrderDate) as firstorder, MAX(o.OrderDate) as lastorder
from Customers C
JOIN Orders o
on o.ShipperId = C.Id or
o.PickupId = C.Id or
o.ConsigneeId = C.Id or
o.DeliveryId = C.Id or
o.BillingId = C.Id
GROUP BY C.Id
那么现在我是否将它用作子查询或游标来回发到客户表?
So now do I use this as a subquery or cursor to post back to the Customers table?
推荐答案
虽然我认为 JOIN
条件不太可能,但您似乎正在尝试这样做?
Although I think the JOIN
criteria is highly unlikely, it looks like you're trying to do this?
我已经修改了 JOIN
条件,但这正是您所追求的.Grouping By
列 OR
是奇数.
I've modified the JOIN
criteria but this is what you're after.
Grouping By
columns that are OR
'd is odd.
;WITH MinOrderDates AS
(
SELECT CustID
,OrderDate = MIN(OrderDate)
FROM Orders
GROUP BY CustID
)
UPDATE C
SET FirstOrderDate = MIN(O.OrderDate)
FROM Customers C
JOIN MinOrderDates O ON C.Id = O.CustID
这就是使用 OR
s
;WITH MinOrderDates AS
(
SELECT ShipperId
,PickupId
,ConsigneeId
,DeliveryId
.BillingId
,OrderDate = MIN(OrderDate)
FROM Orders
GROUP BY ShipperId
,PickupId
,ConsigneeId
,DeliveryId
.BillingId
)
UPDATE C
SET FirstOrderDate = MIN(O.OrderDate)
FROM Customers C
JOIN MinOrderDates O ON o.ShipperId = C.Id or
o.PickupId = C.Id or
o.ConsigneeId = C.Id or
o.DeliveryId = C.Id or
o.BillingId = C.Id
虽然我很难找到你发布的语法的错误.
Though I am having a hard time finding fault with your posted syntax.
这篇关于使用相关子查询更新查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!