SQL SERVER – DELETE From SELECT Statement – Using JOIN in DELETE Statement – Multiple Tables in DELETE Statement

This blog post is inspired from my earlier blog post of UPDATE From SELECT Statement – Using JOIN in UPDATE Statement – Multiple Tables in Update Statement. In the blog post I discussed about how we can use JOIN and multiple tables in the UPDATE statement. There were plenty of the emails after this blog post discussing about using JOIN in the DELETE statement as well using multiple tables in the DELETE statement.

It is totally possible to use JOIN and multiple tables in the DELETE statement. Let us use the same table structure which we had used previously.

Let us see the following example. We have two tables Table 1 and Table 2.

-- 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'
GO
-- 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'
GO

Now let us check the content in the table.

SELECT *
FROM Table1
SELECT *
FROM Table2
GO

Now pay attention to following diagram. Here we have two tables Table1 and Table 2. Our requirement is that we want to delete those two records from Table1 where Table2 Col3 values are “Two-Three” and “Two-Four” and Col1 in both the tables are the same.

I have explained the above statement very easily in following diagram.

When you look at this it looks very simple but when we try to think the solution, I have seen developers coming up with many different solutions for example sometime they write cursor, table variables, local variables etc. However, the easiest and the most clean way is to use JOIN clause in the DELETE statement and use multiple tables in the DELETE statement and do the task.

-- 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')
GO

Now let us select the data from these tables.

-- Check the content of the table
SELECT *
FROM Table1
SELECT *
FROM Table2
GO

As you can see that using JOIN clause in DELETE statement it makes it very easy to update data in one table from another table. You can additionally use MERGE statement to do the same as well, however I personally prefer this method. Let us clean up the clause by dropping the tables which we have created.

DROP TABLE Table1
DROP TABLE Table2
GO

Do let me know if you use any other trick in similar situations. If you do, I would like to learn more about it.

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

8 thoughts on “SQL SERVER – DELETE From SELECT Statement – Using JOIN in DELETE Statement – Multiple Tables in DELETE Statement

  1. Almost similar solution is:

    DELETE Table1
    WHERE Col1 IN (SELECT Col1 FROM Table2 WHERE Col3 IN (‘Two-Three’,’Two-Four’))

  2. HI Pinal

    How are you

    I am a Junior DBA.

    I need some clarifications..

    =========================================
    Can you define How TEMPDB will create at the time of Services start.

    My question is Temp DB will creating by using Model database as a template. Is it right or wrong?

    All the databases including System Database Tempdb use the model database as a template is it right or wrong?
    =========================================
    How to Improve Query Performance?

    How many ways to Improve performance of a Query.

    Recently I said 10+ ways but they need more ways.

    What are that?
    =========================================
    In which replication Distributor and subscriber not run under same instance. While configuring subscriber it is raising an error message like the instane having Distributor and will not allow any subscriber to this instance.
    =========================================

  3. I want to delete the records in second table on the basis of Id present in first table and I also want to delete that Id from the first table using instead of delete trigger
    I have tried it by using select, join and trigger together but could not get it right
    Is there any way around?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s