Post
Ask Question

Employee Salary Updates Using SQL Update with Inner Join Query

Introduction

We'll explore how to update employee salaries while ensuring that each employee has only one salary per job. We'll accomplish this task using the UPDATE statement with INNER JOIN in SQL. Let's dive into a simple example.

Suppose we have two tables: Employees and Salaries. Each employee has only one salary record per job. If there's a salary increment, it should override the previous salary entry in the Salaries table.

How can we implement this using SQL?

First, let's create the Employees and Salaries tables:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50),
    JobID INT
);

CREATE TABLE Salaries (
    EmployeeID INT,
    JobID INT,
    Salary DECIMAL(10, 2),
    CONSTRAINT FK_Employee FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

Next, let's populate the tables with sample data:

INSERT INTO Employees (EmployeeID, Name, JobID) VALUES
(1, 'Anas', 101),
(2, 'Milan', 102);
(3, 'Digant', 103);
(4, 'Shubham', 104);

INSERT INTO Salaries (EmployeeID, JobID, Salary) VALUES
(1, 101, 50000.00),
(2, 102, 40000.00)
(3, 103, 45000.00)
(4, 104, 54000.00);

Now, let's perform a salary update for employee Anas with a new salary of £55,000 for the job ID 101:

UPDATE Salaries
SET Salary = 55000.00
FROM Salaries
INNER JOIN Employees ON Salaries.EmployeeID = Employees.EmployeeID
WHERE Employees.EmployeeID = 1 AND Employees.JobID = 101;

In this example

  • We're updating the Salary column in the Salaries table.
  • We're setting Anas's new salary to £55,000 for the job ID 101.
  • We're using INNER JOIN to ensure that the update applies only to Anas's salary record for the specified job.

Conclusion

By utilizing SQL's UPDATE statement with INNER JOIN, we can efficiently manage employee salary updates while ensuring data integrity. This approach allows us to override previous salary entries seamlessly, making it easier to maintain accurate salary records in the database.


Similar Articles