In a previous blog post, we discussed how to use JOIN and multiple tables in the UPDATE statement. After receiving numerous emails, it became clear that many readers were interested in learning how to use JOIN and multiple tables in the DELETE statement as well. In this blog post, we will explore this topic and provide an example to demonstrate the process.

Let’s start by using the same table structure that we used in the previous blog post. We have two tables, Table1 and Table2.

-- Create table1
CREATE TABLE Table1 (
    Col1 INT,
    Col2 INT,
    Col3 VARCHAR(100)
)

INSERT INTO Table1 (Col1, Col2, Col3)
SELECT 1, 11, 'First'
UNION ALL
SELECT 11, 12, 'Second'
UNION ALL
SELECT 21, 13, 'Third'
UNION ALL
SELECT 31, 14, 'Fourth'

-- Create table2
CREATE TABLE Table2 (
    Col1 INT,
    Col2 INT,
    Col3 VARCHAR(100)
)

INSERT INTO Table2 (Col1, Col2, Col3)
SELECT 1, 21, 'Two-One'
UNION ALL
SELECT 11, 22, 'Two-Two'
UNION ALL
SELECT 21, 23, 'Two-Three'
UNION ALL
SELECT 31, 24, 'Two-Four'

Now, let’s check the content in the tables.

SELECT * FROM Table1
SELECT * FROM Table2

Our requirement is to delete the records from Table1 where the Col3 values in Table2 are “Two-Three” and “Two-Four”, and the Col1 values in both tables are the same. To achieve this, we can use the JOIN clause in the DELETE statement and include multiple tables in the DELETE statement.

-- Delete data from Table1
DELETE Table1
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1
WHERE t2.Col3 IN ('Two-Three', 'Two-Four')

Let’s select the data from these tables to verify the deletion.

-- Check the content of the tables
SELECT * FROM Table1
SELECT * FROM Table2

As you can see, using the JOIN clause in the DELETE statement makes it very easy to delete data from one table based on conditions from another table.

Alternatively, you can also use the MERGE statement to achieve the same result. However, personally, I prefer using the JOIN clause in the DELETE statement for its simplicity and clarity.

Finally, let’s clean up by dropping the tables we created.

-- Clean up
DROP TABLE Table1
DROP TABLE Table2

I hope you found this blog post helpful. If you have any other tricks or methods for similar situations, I would love to learn more about them. Feel free to share your insights in the comments section.