Update query using 2 tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update query using 2 tables

Author

Topic

mgacourt
Starting Member

1 Post

Posted - 2011-01-25 : 13:18:30

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)

Another:

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

I need this for SQL Server, Access and MySQL.

Any help would be much appreciated.

Thanks.

jp.jetti
Starting Member

3 Posts

Posted - 2011-01-25 : 16:17:46

I understood your problem.
But I did it with my own sample tables.

This may help you!

------------------------------------------

Update Table_1

set
[Tab1-Col1] = T2.[Tab2-Col1],
[Tab1-Col2] = T2.[Tab2-Col2]

From
Table_1 T1, Table_2 T2

Where
T1.[Tab1-Col1] = T2.[Tab2-Col1]

------------------------------------------

My sample is working fine.

You can add how many conditions you want in "Where"-clause

All the best!

JP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources