Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Single Update Statement Puzzle

Puzzle Statement

Recently I have attended an Interview, there I was asked this question. Here the challenge was to Update the table using single update statement. So we have a hierarchy table called “Employees” where we have employees and their managers. Here of the employee Jasleen has resigned and they have hired a new manager “Ramesh”. Now we have to update EmployeeName and ManagerName column values from Jasleen to Ramesh at one time since it is a hierarchy table. The challenge here is to do this in a single update statement.

Please check out the sample input values and expected output below.

Sample Input

ID EmployeeName ManagerName Salary
1 Joteep NULL 1000.000
2 Pawan Joteep 900.000
3 Jasleen Joteep 900.000
4 Deepak Jasleen 800.000
5 Sandeep Jasleen 700.000
6 Mayank Jasleen 600.000

Expected Output

ID EmployeeName ManagerName Salary
1 Joteep NULL 1000.000
2 Pawan Joteep 900.000
3 Ramesh Joteep 900.000
4 Deepak Ramesh 800.000
5 Sandeep Ramesh 700.000
6 Mayank Ramesh 600.000

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE Employees
(
       ID INT IDENTITY(1,1) PRIMARY KEY
       ,EmployeeName VARCHAR(50)
       ,ManagerName VARCHAR(50)
       ,Salary DECIMAL (10,3)
)
GO

INSERT INTO Employees VALUES
('Joteep' ,NULL, 1000.00 ),
('Pawan' ,'Joteep', 900.00),
('Jasleen', 'Joteep' ,900.00),
('Deepak' ,'Jasleen' ,800.00),
('Sandeep', 'Jasleen' ,700.00),
('Mayank' ,'Jasleen' ,600.00)
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION 1 | Using 2 Update Statements

--

SELECT * FROM Employees 
GO

BEGIN TRAN

	UPDATE
		  Employees 
	SET 
		  EmployeeName = 'Ramesh' 
	WHERE EmployeeName = 'Jasleen'

	UPDATE
		  Employees 
	SET 
		  ManagerName = 'Ramesh' 
	WHERE ManagerName = 'Jasleen'

COMMIT

SELECT * FROM Employees 
GO

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements