使用相关子查询更新查询 [英] 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

这就是使用 ORs

;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屋！