October 10, 2005 at 5:43 am
Hi,
I have two tables:
Customers:
CustomerID
CustomerName
dtFirstOrder
dtLastOrder
Orders:
OrderID
CustomerID
dtOrderDate
dtOrderTotal
The dtFirstOrder and dtLastOrder hold date values for each customer to show when their first and last orders were respectively.
I need to run a query that will update all the records in the customers table with the required values from the Orders table.
So the following will give me the list of all customers and their first/last order dates:
SELECT
dbo.Customers.CustomerID,
MIN(dbo.Orders.dtOrderDate) AS FirstOrder,
MAX(dbo.Orders.dtOrderDate) AS LastOrder
FROM
dbo.Customers INNER JOIN dbo.Orders ON
dbo.Customers.CustomerID = dbo.Orders.CustomerID
GROUP BY
dbo.Customers.CustomerID
Now I need to feed that back into the Customers table.
I tried adding:
UPDATE Customers SET
at the beginning but I constantly get an error.
Anyone spot where I've gone wrong?
Andy
October 10, 2005 at 6:21 am
Probably a incorrect syntax. Here is a statement that should work:
Update Customer
set FirstOrderDt = New.FirstOrderDt
, LastOrderDt = New.LastOrderDt
FROM (
SELECT
dbo.Customers.CustomerID,
MIN(dbo.Orders.dtOrderDate) AS FirstOrderDt,
MAX(dbo.Orders.dtOrderDate) AS LastOrderDt
FROM dbo.Customers
INNER JOIN dbo.Orders
ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
GROUP BY dbo.Customers.CustomerID
) as New
WHERE Customer.CustomerID = New.CustomerID
SQL = Scarcely Qualifies as a Language
October 10, 2005 at 7:23 am
Many Thanks that fixed it!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply