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.
Reference: Pinal Dave (https://blog.sqlauthority.com)
21 Comments. Leave new
Almost similar solution is:
DELETE Table1
WHERE Col1 IN (SELECT Col1 FROM Table2 WHERE Col3 IN (‘Two-Three’,’Two-Four’))
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.
=========================================
AFAIK, yes. Every DB is created using Model as a model.
Join is better than sub-query.
Why? And if it is “performance” – does it really matter or isn’r readability more important?
dear harsh , this almost same but performance of your query low .
dear alkesh, check this also.
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?
I want to delete data from both tables Table1 and Table2 with col1 values using join
is it possible ?
I want to delete data from both the tables at a time with col1 values using join.
— 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?
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
I want to delete two table records when meets the criteria
You will have to write two different delete statements with the same condition.
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
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.
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’..
this post is very useful
awesome information #Thanks
Thank you brother
Hallo Sir, And if we want to punch the two data in the two tables at the same time, what exactly should we write?