SQL Server 2019 (Linux)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- Create Employee table
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT,
    Salary DECIMAL(10, 2)
);

-- Insert sample data into Employee table
INSERT INTO Employee (EmployeeID, FirstName, LastName, DepartmentID, Salary)
VALUES
    (1, 'John', 'Doe', 1, 50000.00),
    (2, 'Jane', 'Smith', 2, 60000.00),
    (3, 'Michael', 'Johnson', 1, 55000.00),
    (4, 'Emily', 'Williams', 3, 70000.00),
    (5, 'David', 'Brown', 2, 62000.00);
5 rows affected

SELECT * FROM Employee;


EmployeeID
FirstName
LastName
DepartmentID
Salary


1
John
Doe
1
50000.00

2
Jane
Smith
2
60000.00

3
Michael
Johnson
1
55000.00

4
Emily
Williams
3
70000.00

5
David
Brown
2
62000.00


-- Create Department table
CREATE TABLE Department (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);

-- Insert sample data into Department table
INSERT INTO Department (DepartmentID, DepartmentName)
VALUES
    (1, 'Sales'),
    (2, 'Marketing'),
    (3, 'Engineering'),
    (4, 'Human Resources');
4 rows affected

SELECT * FROM Department;


DepartmentID
DepartmentName


1
Sales

2
Marketing

3
Engineering

4
Human Resources


WITH EngineeringSalaryUpdate
AS (SELECT EmployeeID,
           Salary AS OldSalary,
           Salary * 1.1 AS NewSalary
    FROM Employee AS E
        INNER JOIN Department AS D
            ON E.DepartmentID = D.DepartmentID
    WHERE D.DepartmentName = 'Engineering')
UPDATE EngineeringSalaryUpdate
SET OldSalary = NewSalary
1 rows affected

SELECT * FROM Employee;


EmployeeID
FirstName
LastName
DepartmentID
Salary


1
John
Doe
1
50000.00

2
Jane
Smith
2
60000.00

3
Michael
Johnson
1
55000.00

4
Emily
Williams
3
77000.00

5
David
Brown
2
62000.00

EmployeeID	FirstName	LastName	DepartmentID	Salary
1	John	Doe	1	50000.00
2	Jane	Smith	2	60000.00
3	Michael	Johnson	1	55000.00
4	Emily	Williams	3	70000.00
5	David	Brown	2	62000.00