Results 1 to 2 of 2

Thread: Update query using 2 tables

  1. #1
    Join Date
    Jan 2011
    Posts
    1

    Update query using 2 tables

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Your UPDATE statement looks quite complicated, it may be a good idea to use intermediate temporary table and use that to update the main table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •