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;
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;
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;
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.
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.
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;
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;
DELETE emp
FROM Employee AS emp
LEFT OUTER JOIN ContractRenewals AS ren
ON ren.EmployeeId = emp.id
WHERE ren.EmployeeId IS NULL;
