In this post we will see how to use JOIN for deleting data from SQl server table. Let us first create tables which we will use for understanding the deletion process using JOINS.

-- Create table1
CREATE TABLE #Table1 (Col1 INT, Col2 VARCHAR(50))
INSERT INTO #Table1 (Col1, Col2)
SELECT 11,'First'
UNION ALL
SELECT 12, 'Second'
UNION ALL
SELECT 13, 'Third'
UNION ALL
SELECT 14, 'Fourth'
UNION ALL
SELECT 14, 'Fifth'
GO
-- Create table2
CREATE TABLE #Table2 (Col1 INT, Col2 VARCHAR(50))
INSERT INTO #Table2 (Col1, Col2)
SELECT 21, 'TwentyOne'
UNION ALL
SELECT 22, 'TwentyTwo'
UNION ALL
SELECT 23, 'TwentyThree'
UNION ALL
SELECT 14, 'TwentyFour'
GO

Let us say we want to delete data from #Table1 based on matching values in col1 of both tables. The query will be as below:

DELETE #Table1
FROM #Table1 t1
INNER JOIN #Table2 t2 ON t1.Col1 = t2.Col1
GO

The above query will delete two rows from #Table1 where col1 value is 14.Additionally we can also use the WHERE clause if we want to narrow down are delete based on values in columns.

DELETE #Table1
FROM #Table1 t1
INNER JOIN #Table2 t2 ON t1.Col1 = t2.Col1
WHERE t1.Col2 IN ('Fourth')
GO

The above query will delete one row from #Table1 where col1 value is 14 and Col2 value is 'Fourth'