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.

Solarwinds

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.

SQL SERVER - DELETE From SELECT Statement - Using JOIN in DELETE Statement - Multiple Tables in DELETE Statement deletejoin

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

SQL SERVER - DELETE From SELECT Statement - Using JOIN in DELETE Statement - Multiple Tables in DELETE Statement deletejoin1

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.

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

Solarwinds
Previous Post
SQLAuthority News – Presenting 3 Technical Sessions at Great Indian Developer Summit – May 7, 2013 – Bangalore
Next Post
SQL SERVER – Weekly Series – Memory Lane – #027

Related Posts

18 Comments. Leave new

  • Almost similar solution is:

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

    Reply
  • 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.
    =========================================

    Reply
  • Suman Kumar Jha (@SumnJha)
    May 3, 2013 2:54 pm

    Join is better than sub-query.

    Reply
  • dear harsh , this almost same but performance of your query low .

    Reply
  • Shailesh Aggarwal
    November 6, 2013 12:04 pm

    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?

    Reply
  • rajkumarpalnati
    April 29, 2014 3:08 pm

    I want to delete data from both tables Table1 and Table2 with col1 values using join
    is it possible ?

    Reply
  • rajkumarpalnati
    April 29, 2014 3:11 pm

    I want to delete data from both the tables at a time with col1 values using join.

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

    this query also yields the same results right?

    Reply
  • how to delete inner join query to set button acc to id i use a query thatnot work properly
    echo ‘DELETE’;

    whats the probem in this code

    Reply
  • Chandana De Silva
    August 27, 2016 4:17 pm

    I want to delete two table records when meets the criteria

    Reply
  • Dharmendra kumar sah
    September 21, 2016 1:40 pm

    i want to delete from two table same query but when i write query goes delete only from one table,
    Declare @Ahead
    set @Ahead=(select AccountHeadID from tblAccountHead join dbo.Agent on AgentAccountHeadID=AccountHeadID where AgentID=@AgentID);

    DELETE FROM dbo.tblAccountHead Where AccountHeadID=@Ahead

    Reply
  • Dharmendra kumar sah
    September 21, 2016 1:44 pm

    DELETE FROM dbo.Agent where AgentID = @AgentID This is working but
    DELETE FROM dbo.tblAccountHead Where AccountHeadID=@Ahead this is not working

    when i write this all query in new query and after run working but in my query only one runing.

    Reply
  • update StudentEntryTB
    SET class = 12th
    WHERE rollno=’104′

    hi, i’m new to SQL and i have 1 problem can any one tell me what is error ?? it is giving INCORRECT ERROR NEAR ‘WHERE’..

    Reply
  • this post is very useful

    Reply
  • awesome information #Thanks

    Reply

Leave a Reply

Menu