title: Update Query with Correlated Subquery
body: <p>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.</p>
  
  <p>I need to come up with a couple new fields in the Customer table based on the Orders table, FirstOrder and LastOrder.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>So something like:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>Just can't seem to find out how to tie the subquery with the main update query.</p>
  
  <p>Thanks,
  -Sid</p>
  
  <hr>
  
  <p>EDIT:
  Here's the SELECT that's working based on MarkD's suggestion:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>So now do I use this as a subquery or cursor to post back to the Customers table?</p>