Microsoft SQL Server Join Delete using Join
Example
Joins can also be used in a DELETE statement. Given a schema as follows:
CREATE TABLE Users (
UserId int NOT NULL,
AccountId int NOT NULL,
RealName nvarchar(200) NOT NULL
)
CREATE TABLE Preferences (
UserId int NOT NULL,
SomeSetting bit NOT NULL
)
We can delete rows from the Preferences table, filtering by a predicate on the Users table as follows:
DELETE p
FROM Users u
INNER JOIN Preferences p ON u.UserId = p.UserId
WHERE u.AccountId = 1234
Here p is an alias for Preferences defined in the FROM clause of the statement and we only delete rows that have a matching AccountId from the Users table.
