Link to home
Start Free TrialLog in
Avatar of dunkin1969
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.
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

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'
Avatar of dunkin1969
dunkin1969

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.
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.
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.
ASKER CERTIFIED SOLUTION
Avatar of Bruce Cadiz
Bruce Cadiz
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Open in new window

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

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Oh yeah. I think cte Is not supported in PDW. Then try Scott/c3 suggestion
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!!!
Thanks as always for your expertise ScottPletcher!!