July 9, 2009 at 7:13 am
Is it possible to have a group by clause in the subquery of an update statement? I'm trying to do the following, and I'm not having any luck so far. Help would be appreciated.
UPDATE @ReportFields
SET EarliestNextVisitDate = b.EarliestDate
FROM (SELECT ContactID, MIN(NextVisitDate) AS EarliestDate FROM @ReportFields GROUP BY ContactID) b
WHERE ContactID = b.ContactID
@ReportFields is a table with several rows for each ContactID. I want to take the minimum value of NextVisitDate (for each ContactID) and plug it into the EarliestNextVisitDate for each row (with a matching ContactID). Does that make sense?
July 9, 2009 at 7:42 am
I think either of these will queries will work. Had you posted some sample data as outlined in the first link in my signature I would have tested the queries as well.
UPDATE @ReportFields
SET EarliestNextVisitDate = b.EarliestDate
FROM
@ReportFields RF JOIN
(SELECT
ContactID,
MIN(NextVisitDate) AS EarliestDate
FROM
@ReportFields
GROUP BY
ContactID) b ON
RF.ContactID = b.ContactID
UPDATE RF
SET EarliestNextVisitDate = (SELECT MIN(b.NextVisitDate) FROM @ReportFields b WHERE RF.ContactID = b.ContactID GROUP BY b.ContactID)
FROM
@ReportFields RF
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 9, 2009 at 12:54 pm
That worked perfectly. Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply