Updating & deleting records with no match in another table

Several weeks ago I posted an article about Non-SELECT Joins in T-SQL and MySQL. The examples only covered INNER JOINS but sometimes we need to update, or delete, records in a table that do not have a corresponding record in another table. I’m going to illustrate the various methods for doing this in SQL Server and MySQL.  

SQL Server create tables and data

CREATE TABLE Employee
(
	id INTEGER IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
	FirstName VARCHAR(20) NOT NULL,
	LastName VARCHAR(30) NOT NULL,
	Sex CHAR(1) NOT NULL,
	Dob DATE NOT NULL,
	Position VARCHAR(30) NOT NULL DEFAULT 'Unassigned',
	Salary MONEY NOT NULL,
	SalaryAdjustment BIT NULL
);
-- Insert some Employees
INSERT INTO Employee
(
	FirstName,
	LastName,
	Sex,
	Dob,
	Position,
	Salary
)
VALUES ('Dave','Smith','M','1978-01-01','Product Director',35000.00),
('Joe','Bloggs','M','1973-03-11','CEO',100000.00),
('John','Doe','M','1956-09-29','CFO',95000.00),
('Karen','Smith','F','1980-02-03','Marketing',60000.00),
('Clare','Jones','F','1970-10-30','Accounts',30000.00),
('Fernando','Cruz','M','1978-01-01','Technical Support',30000.00),
('Steve','Campbell','M','1975-05-17','IT Manager',45000.00);
-- Create a table for Salary adjustments
CREATE TABLE SalaryAdjustment
(
	EmployeeId INTEGER NOT NULL PRIMARY KEY CLUSTERED,
	NewSalary MONEY NOT NULL
);
-- Salary Adjustments
INSERT INTO SalaryAdjustment
(
	EmployeeId,
	NewSalary
)
VALUES
(2, 80000.00),(6, 40000.00);
 
-- Just a simple table containing Employee IDs
CREATE TABLE ContractRenewals
(
	EmployeeId INTEGER NOT NULL PRIMARY KEY CLUSTERED
);
-- Insert some EmployeeIds
INSERT INTO ContractRenewals (EmployeeId)
VALUES (2),(5);

MySQL create tables and data

CREATE TABLE Employee
(
	id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
	FirstName VARCHAR(20) NOT NULL,
	LastName VARCHAR(30) NOT NULL,
	Sex CHAR(1) NOT NULL,
	Dob DATE NOT NULL,
	Position VARCHAR(30) NOT NULL DEFAULT 'Unassigned',
	Salary DECIMAL(10,2) NOT NULL,
	SalaryAdjustment TINYINT NULL
);
# Insert some Employees
INSERT INTO Employee
(
	FirstName,
	LastName,
	Sex,
	Dob,
	Position,
	Salary
)
VALUES ('Dave','Smith','M','1978-01-01','Product Director',35000.00),
('Joe','Bloggs','M','1973-03-11','CEO',100000.00),
('John','Doe','M','1956-09-29','CFO',95000.00),
('Karen','Smith','F','1980-02-03','Marketing',60000.00),
('Clare','Jones','F','1970-10-30','Accounts',30000.00),
('Fernando','Cruz','M','1978-01-01','Technical Support',30000.00),
('Steve','Campbell','M','1975-05-17','IT Manager',45000.00);
# Create a table for Salary adjustments
CREATE TABLE SalaryAdjustment
(
	EmployeeId INTEGER NOT NULL PRIMARY KEY,
	NewSalary DECIMAL(10,2) NOT NULL
);
# Salary Adjustments
INSERT INTO SalaryAdjustment
(
	EmployeeId,
	NewSalary
)
VALUES
(2, 80000.00),(6, 40000.00);
# Just a simple table containing Employee IDs
CREATE TABLE ContractRenewals
(
	EmployeeId INTEGER NOT NULL PRIMARY KEY
);
# Insert some EmployeeIds
INSERT INTO ContractRenewals (EmployeeId)
VALUES (2),(5);

SQL Server: UPDATE a table with no matching row in another table

Initially our data will look like this;

Employee table after update

Our task is to update the value of Employee.SalaryAdjustment to 0 if the employee’s ID does not exist in the SalaryAdjustment table. Of course we could achieve this with a simple subquery.

UPDATE Employee
SET SalaryAdjustment = 0
WHERE id NOT IN (SELECT EmployeeId
		 FROM SalaryAdjustment);

The query is easy to understand but wouldn’t perform great on large datasets. Another method would be to use to NOT EXISTS.

UPDATE Employee
SET SalaryAdjustment = 0
WHERE NOT EXISTS (SELECT *
		  FROM SalaryAdjustment sal
		  WHERE sal.EmployeeId = Employee.id);

This method should provide good performance provided there is an appropriate index to support the query. The final method uses a LEFT JOIN.

UPDATE emp
SET emp.SalaryAdjustment = 0
FROM Employee AS emp
LEFT OUTER JOIN SalaryAdjustment AS sal 
	ON emp.id = sal.EmployeeId
WHERE sal.EmployeeId IS NULL;

After running each of these queries, not forgetting to set all Employee.SalaryAdjustment values back to NULL, the Employee table will look like this after each one;

Empoyee table after each update

MySQL: UPDATE a table with no matching row in another table

The first two update methods, subquery and NOT EXISTS, shown above are syntactically identical in MySQL (hooraay for standards!) so I won’t repeat them here. The Employee table in your MySQL database will look like;

Employee table before update

UPDATE Employee AS emp
LEFT JOIN SalaryAdjustment AS sal 
	ON sal.EmployeeId = emp.id
SET emp.SalaryAdjustment = 0
WHERE sal.EmployeeId IS NULL;

I commented in my previous article that I find the MySQL Join syntax so much more natural. I’m still finding this to be the case. Here’s what the Employee table looks like after the update. You can see that it has updated all the employee records that are not found in SalaryAdjustment.

Employee table after update

SQL Server: Delete records with no matching row in another table

When deleting records we can also take advantage of the methods above; subqueries, NOT EXISTS and a LEFT JOIN.

Employee table

The ContractRenewals table contains the following data;

ContractRenewals table

We want to remove all the records in Employee that aren’t matched in ContractRenewals.

Using a subquery:

DELETE 
FROM Employee 
WHERE id NOT IN (SELECT EmployeeId
		 FROM ContractRenewals);

Using NOT EXISTS:

DELETE 
FROM Employee
WHERE NOT EXISTS (SELECT *
		  FROM ContractRenewals
		  WHERE ContractRenewals.EmployeeId = Employee.Id);

Using a LEFT JOIN:

DELETE Employee
FROM Employee AS emp
LEFT OUTER JOIN ContractRenewals AS ren 
	ON ren.EmployeeId = emp.id
WHERE ren.EmployeeId IS NULL;

Running each of these queries will produce the same end result. The Employee table should contain the following data;

Employee table after delete

MySQL: Delete records with no matching row in another table

Again, as the subquery and NOT EXISTS, methods are syntactically identical in MySQL I will only show the LEFT JOIN method here;

Employee table before delete

DELETE emp
FROM Employee AS emp
LEFT OUTER JOIN ContractRenewals AS ren 
	ON ren.EmployeeId = emp.id
WHERE ren.EmployeeId IS NULL;

Employee table after delete


One Comment

  1. Steve Tuinstra says:

    Thanks. This is exactly what I was looking for.

Leave a Reply