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.
dunkin1969Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
IN requires only one column in the ( subquery ), and you have two columns:  Customer_ID and Last_Order.

Try this (making a few assumptions here)...
UPDATE Customers
SET c.Last_Order_Date = o.Last_Order
FROM Customers c
  JOIN  (  
      SELECT Customer_ID, MAX(Order_Date) AS Last_Order 
      FROM Orders 
      GROUP BY Customer_ID ) o ON c.Customer_ID = o.Customer_ID AND c.Last_Order_Date = o.Last_Order

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'
0
dunkin1969Author Commented:
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.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dunkin1969Author Commented:
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.
0
Bruce CadizQuality SpecialistCommented:
You should be able to do a correlated update like this:
UPDATE customers c
   SET c.last_order_date = (SELECT MAX(o.order_date) 
                              FROM orders o
                             WHERE o.customer_id = c.customer_id)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dunkin1969Author Commented:
thanks c336914 but I'm getting error "incorrect syntax near 'c'
0
Bruce CadizQuality SpecialistCommented:
What database / version are you using?
0
ralmadaCommented:
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

Open in new window

0
dunkin1969Author Commented:
Thanks ralmada.  I am tied up at the moment but will try your changes later this evening :-)
0
ralmadaCommented:
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

Open in new window

0
Scott PletcherSenior DBACommented:
c336914's method is the most straightforward, just needs the alias removed from the outer query.  NO points for me please:

UPDATE customers
SET last_order_date = (SELECT MAX(o.order_date)
                              FROM orders o
                             WHERE o.customer_id = customers.customer_id)
0
dunkin1969Author Commented:
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.
0
ralmadaCommented:
Oh yeah. I think cte Is not supported in PDW. Then try Scott/c3 suggestion
0
dunkin1969Author Commented:
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!!!
0
dunkin1969Author Commented:
Thanks as always for your expertise ScottPletcher!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.