Update columns using inner join

Vamsi k

7y860

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.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sonu Gupta7y ago
Hi,

Following example of update with inner join in SQL Server

CREATE table #tempDept (DeptID int, DeptName varchar(50));
CREATE table #tempEmployee (EmpID int, EmpName varchar(50),DeptID int,DeptName varchar(50));

INSERT INTO #tempDept VALUES (1, 'Admin'),(2, 'HR'),(3, 'IT');
INSERT INTO #tempEmployee VALUES (1, 'AK',1,'IT'),(2, 'RJ',1,'IT'),(3, 'SK',2,'IT');

UPDATE T SET T.DeptName=SC.DeptName FROM #tempEmployee T
INNER JOIN #tempDept SC ON SC.DeptID=T.DeptID
0
Chittaranjan Swain7y ago
Hi,

Refer below links,
https://www.tech-recipes.com/rx/56823/delete-update-rows-using-inner-join-sql-server/
https://stackoverflow.com/questions/9894353/update-query-with-inner-join-between-tables-in-2-different-databases-on-1-server
0