Update Query with Correlated Subquery

问题:

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.

I need to come up with a couple new fields in the Customer table based on the Orders table, FirstOrder and LastOrder.

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.

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.

So something like:

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.

Thanks, -Sid

---

EDIT: 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?

回答1:

Although I think the JOIN criteria is highly unlikely, it looks like you're trying to do this?

EDIT: 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

This is what your query would look like with the 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 

EDIT: Though I am having a hard time finding fault with your posted syntax.

回答2:

Try this

UPDATE customers
SET FirstOrderDate = 
(Select MIN(OrderDate)
FROM Orders
WHERE ShipperId = Customers.Id or
PickupId = Customers.Id or
ConsigneeId = Customers.Id or
DeliveryId = Customers.Id or
BillingId = Customers.Id)