August 6, 2014 at 12:46 pm
Hi,
Attached is the data workbook. I have listed two tables table 1 has some data. I have to update table 2 [reports] column from table 1 [reports] using self join
I should get as table 2 after updating
August 6, 2014 at 1:34 pm
Something like this?
DECLARE @Temp TABLE
(
EmailId varchar(25) NOT NULL
, EmpName varchar(25) NOT NULL
, Team varchar(25) NOT NULL
, ID int NOT NULL
, Reports int NOT NULL
);
DECLARE @Temp2 TABLE
(
EmpName varchar(25) NOT NULL
, Reports varchar(25) NOT NULL
);
INSERT INTO @Temp (EmailId, EmpName, Team, ID, Reports) VALUES
('Rob@gmail.com', 'Rob', 'HR', 1231, 1001)
, ('sin@gmail.com', 'sin', 'Finance', 1245, 1234)
, ('van@gmail.com', 'van', 'Sales', 1234, 1245)
, ('garr@gmail.com', 'gar', 'Sales', 1001, 1231);
MERGE INTO @Temp2 AS TARGET
USING
(
SELECT
Emp.EmpName
, Boss.EmpName AS [Reports]
FROM @Temp Emp
JOIn @Temp Boss ON Boss.ID = Emp.Reports
) AS SOURCE ON
SOURCE.EmpName = TARGET.EmpName
WHEN NOT MATCHED BY TARGET THEN INSERT (EmpName, Reports) VALUES (SOURCE.EmpName, SOURCE.Reports)
WHEN MATCHED AND SOURCE.Reports <> TARGET.Reports THEN UPDATE SET TARGET.Reports = SOURCE.Reports;
SELECT * FROM @Temp2;
blog: http://www.codemuddler.com
twitter: http://www.twitter.com/CodeMuddler
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply