Vamsi k

Vamsi k

  • NA
  • 296
  • 46.4k

Update columns using inner join

Aug 14 2019 12:07 AM
Hi All,
 
We have two tables a,b
 
Every month we need to update the 3 column values in a table with b table columns values based on 4 column matching.
 
So when we are doing like this old values are replacing with new values but we want old values and new values as well.
 
Query 
Ex: Suppose in this month if we write below query it will update with new values but next month these old values are overridden with new OCC values. We need both Current month and Nextmonth values also. 
 
Update My_DATA set [OCC Line Item Code]=O.[Line Item Code],
[OCC Desc]=O.[Line Item Code Desc],
[OCC Amount]=O.[Amount]
from [dbo].[My_DATA] f inner join OCC_DATA O on f.Vendor_Name= O.[Vendor Name]
AND f.BAN=O.[BAN] AND f.Bill_Date=O.[Bill Date] AND f.[Stripped_EC_Circuit_ID]=O.[Stripped Vendor Circuit ID]
So the above query will update My_Data Table values every month with new values of OCC data table. But i want old values also along with new values. 
 
Please provide your answers.

Answers (2)