dunkin1969
asked on
SQL UPDATE Column with Result from Subquery
Hello experts,
I hope is phrased my question properly.
I have a pre-existing table called CUSTOMERS, that has columns CustomerID and Last_Order_Date. I want to update each Customer_ID with their last order date from another table (Orders):
UPDATE Customers
SET Last_Order_Date = Last_Order
WHERE Customer_ID IN
( SELECT Customer_ID, MAX(Order_Date) AS Last_Order
FROM Orders
GROUP BY Customer_ID )
Can you help fix what I have above to make it work? thank you.
I hope is phrased my question properly.
I have a pre-existing table called CUSTOMERS, that has columns CustomerID and Last_Order_Date. I want to update each Customer_ID with their last order date from another table (Orders):
UPDATE Customers
SET Last_Order_Date = Last_Order
WHERE Customer_ID IN
( SELECT Customer_ID, MAX(Order_Date) AS Last_Order
FROM Orders
GROUP BY Customer_ID )
Can you help fix what I have above to make it work? thank you.
ASKER
Jim,
I received this error:
"FROM clasue in UPDATE and DELETE statements cannot contain subquery sources or joins. Severity 16."
Also, I see you are joining on c.Last_Order_Date = o.Last_Order. That would cause no results right? Because c.Last_Order_Date is null in the Customers table. In other words, that column has no data for any of my Customers.... which is why I'm doing the UPDATE statement to populate it.
Thanks.
I received this error:
"FROM clasue in UPDATE and DELETE statements cannot contain subquery sources or joins. Severity 16."
Also, I see you are joining on c.Last_Order_Date = o.Last_Order. That would cause no results right? Because c.Last_Order_Date is null in the Customers table. In other words, that column has no data for any of my Customers.... which is why I'm doing the UPDATE statement to populate it.
Thanks.
Tell you what. Give us a data mockup, both source data and expected output, of what you're trying to pull off here. That way experts don't have to make assumptions.
ASKER
Here's a quick and dirty example of what I have (I can't upload real data).
Customers table:
Customer_ID Address Last_Order_Date
100 blah blah
200 hee hee
500 hoo hoo
Orders table:
Customer_ID Order_ID Order_Date
100 701 1/11/2014
100 755 2/18/2014
100 888 3/15/2014
500 222 6/19/2014
500 414 8/12/2014
500 241 10/22/2014
Run an UPDATE to produce most recent Order_Date for each Customer (some customers may not have any orders).
After update to Customers table:
Customer_ID Address Last_Order_Date
100 blah blah 3/15/2014
200 hee hee
500 hoo hoo 10/22/2014
Hope this helps. Sorry I couldn't explain it better.
Customers table:
Customer_ID Address Last_Order_Date
100 blah blah
200 hee hee
500 hoo hoo
Orders table:
Customer_ID Order_ID Order_Date
100 701 1/11/2014
100 755 2/18/2014
100 888 3/15/2014
500 222 6/19/2014
500 414 8/12/2014
500 241 10/22/2014
Run an UPDATE to produce most recent Order_Date for each Customer (some customers may not have any orders).
After update to Customers table:
Customer_ID Address Last_Order_Date
100 blah blah 3/15/2014
200 hee hee
500 hoo hoo 10/22/2014
Hope this helps. Sorry I couldn't explain it better.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks c336914 but I'm getting error "incorrect syntax near 'c'
What database / version are you using?
A few tweaks there:
UPDATE c
SET c.Last_Order_Date = o.Last_Order
FROM Customers c
INNER JOIN (SELECT Customer_ID, MAX(Order_Date) AS Last_Order
FROM Orders
GROUP BY Customer_ID) o ON c.Customer_ID = o.Customer_ID
ASKER
Thanks ralmada. I am tied up at the moment but will try your changes later this evening :-)
or you can do this as well:
;with CTE (
SELECT Customer_ID, MAX(Order_Date) AS Last_Order
FROM Orders
GROUP BY Customer_ID
)
update c
set c.Last_orders = o.Last_Order
from Customers c
inner join CTE b on c.Customer_ID = o.Customer_ID
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ramalda, I had some time so tried your first method and got this error:
"FROM clause in UPDATE and DELETE statements cannot contain subquery sources or joins. Severity 16."
I will try your second suggestion later, but I don't think I can use CTE on Microsoft PDW.
"FROM clause in UPDATE and DELETE statements cannot contain subquery sources or joins. Severity 16."
I will try your second suggestion later, but I don't think I can use CTE on Microsoft PDW.
Oh yeah. I think cte Is not supported in PDW. Then try Scott/c3 suggestion
ASKER
Thanks everyone for your help! I will use c336914's method with the alias removed from the outer query as Scott suggested. It now works perfectly without errors!! THANKS AGAIN!!!
ASKER
Thanks as always for your expertise ScottPletcher!!
Try this (making a few assumptions here)...
Open in new window
I have this demonstrated in my article SQL Server GROUP BY Solutions, scroll down to point '5. Aggregate AND values from a single row that make up the aggregate: Subquery'