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