Hi

We need to update a table from another one. The target table AdditItems has to be updated with values from another table called AutoCRCustInvMain. The table AutoCRCustInvMain has more than one line with the same data but we want to use the lines where DateTimeBilled is the most recent one. I have been playing with sql but can't seem to get it to work:

One attempt:

UPDATE AdditItems, AutoCRCustInvMain SET AdditItems.AggregatedMinutesOrCalls = AutoCRCustInvMain.AggregatedMinutesOrCalls, AdditItems.LastBilledDate = AutoCRCustInvMain.LastBilledDate, AdditItems.NextBillPeriod = AutoCRCustInvMain.NextBillPeriod WHERE AdditItems.CustomerIndex = 1 AND AutoCRCustInvMain.ContactIndex = 1 AND AutoCRCustInvMain.ContactType = 0 AND AdditItems.ItemRecurrence = AutoCRCustInvMain.ItemUsageType AND AdditItems.ItemName = AutoCRCustInvMain.ItemCriteria AND (AdditItems.ItemType = 1 OR AdditItems.ItemType = 2) AND AutoCRCustInvMain.ItemType = 4 HAVING MAX(DateTimeBilled)

Another:

UPDATE AdditItems SET AdditItems.AggregatedMinutesOrCalls = AutoCRCustInvMain.AggregatedMinutesOrCalls, AdditItems.LastBilledDate = AutoCRCustInvMain.LastBilledDate, AdditItems.NextBillPeriod = AutoCRCustInvMain.NextBillPeriod WHERE AdditItems.ID IN (SELECT Max(AutoCRCustInvMain.DateTimeBilled) AS MaxOfDateTimeBilled, AutoCRCustInvMain.AggregatedMinutesOrCalls, AutoCRCustInvMain.LastBilledDate, AutoCRCustInvMain.NextBillPeriod, AutoCRCustInvMain.ItemCriteria
FROM AdditItems INNER JOIN AutoCRCustInvMain ON (AutoCRCustInvMain.ItemCriteria = AdditItems.ItemName AND AdditItems.ItemRecurrence = AutoCRCustInvMain.ItemUsageType) WHERE AutoCRCustInvMain.ContactIndex = 1 AND AutoCRCustInvMain.ContactType = 0 AND AutoCRCustInvMain.ItemType = 4 AND (AdditItems.ItemType = 1 OR AdditItems.ItemType = 2) AND AdditItems.CustomerIndex = 1 GROUP BY AutoCRCustInvMain.AggregatedMinutesOrCalls, AutoCRCustInvMain.LastBilledDate, AutoCRCustInvMain.NextBillPeriod, AutoCRCustInvMain.ItemCriteria)

I need this for SQL Server.

Any help would be much appreciated.

Thanks.