T-SQL: Selecting rows to delete via joins

Question

Scenario:

Let's say I have two tables, TableA and TableB. TableB's primary key is a single column (BId), and is a foreign key column in TableA.

In my situation, I want to remove all rows in TableA that are linked with specific rows in TableB: Can I do that through joins? Delete all rows that are pulled in from the joins?

DELETE FROM TableA 
FROM
   TableA a
   INNER JOIN TableB b
      ON b.BId = a.BId
      AND [my filter condition]

Or am I forced to do this:

DELETE FROM TableA
WHERE
   BId IN (SELECT BId FROM TableB WHERE [my filter condition])

The reason I ask is it seems to me that the first option would be much more effecient when dealing with larger tables.

Thanks!

score 790 · Accepted Answer · edited Dec 25 '14 at 13:21

790

DELETE TableA
FROM   TableA a
       INNER JOIN TableB b
               ON b.Bid = a.Bid
                  AND [my filter condition]

should work

edited Dec 25 '14 at 13:21

Pரதீப்

91,748
19
131
172

answered Jan 13 '09 at 16:50

TheTXI

37,429
10
86
110

2

I used And [my filter condition] on the join instead of a Where clause. I would imagine both would work, but the filter condition on the join will limit your results from the join. – TheTXI Jan 13 '09 at 16:51
11

One question. Why do we need to write 'DELETE TableA FROM' instead of 'DELETE FROM'? I see it works only in this case, but why? – UnDiUdin Oct 20 '10 at 13:53
72

I think because you have to indicate which table to delete records from. I just ran a query with the syntax `DELETE TableA, TableB ...` and that actually deleted the relevant records from both. Nice. – Andrew Dec 24 '10 at 23:23
2

In PostgreSQL syntax with join doesn't work but it is possible to use "using" keyword. `DELETE from TableA a using TableB b where b.Bid = a.Bid and [my filter condition]` – bartolo-otrit Jun 13 '12 at 08:00
9

In MySQL you would get an error "Unknown table 'TableA' in MULTI DELETE" and that is because you declared an alias for TableA (a). Small adjustment: `DELETE a FROM TableA a INNER JOIN TableB b on b.Bid = a.Bid and [my filter condition]` – masam Aug 10 '12 at 06:34
1

Works for me in SQL2008 +1 – Andez Feb 11 '13 at 17:09
1

I have a similar question. I want to delete from both tables A and B. How do I do it ? Question - http://stackoverflow.com/questions/21620018/delete-rows-from-tables-using-joins Thanks. – Steam Feb 07 '14 at 05:31
1

This answer just helped me...Thank you and thanks for knowledge bases. – J.S. Orris Apr 17 '14 at 19:29
1

`DELETE a FROM TableA a ... ...` is the correct way – Glauco Cucchiar Oct 22 '19 at 10:31
1

This is not the best solution. Joins should only be used when the user needs to retrieve data from multiple tables. For DMLs like updates and deletes, you only need to refine the population. Thus, a delete query should be against 1 table, and then you join within a subquery. see my answer below. – 1c1cle Dec 03 '19 at 17:35

score 277 · Answer 2 · answered Jan 13 '09 at 16:51

277

I would use this syntax

Delete a 
from TableA a
Inner Join TableB b
on  a.BId = b.BId
WHERE [filter condition]

answered Jan 13 '09 at 16:51

cmsjr

56,771
11
70
62

9

I prefer this syntax as well, seems to make a little more sense logically what is going on. Also, I know you can use this same type of syntax for an UPDATE. – Adam Nofsinger Nov 24 '10 at 21:58
1

I prefer it too, because the placement of the table alias after the DELETE has always seemed more intuitive to me as to what is being deleted. – Jagd Jan 31 '12 at 19:22
15

Indeed, this is preferred for me as well. Specifically in cases where I need to actually join on the same table (e.g. for deleting duplicate records). In that case, I need to use an alias for the "side" I'm deleting from and this syntax makes it super clear I'm deleting from the duplicates alias. – Chris Simmons Feb 21 '12 at 15:32

score 31 · Answer 3 · answered Jan 13 '09 at 16:50

31

Yes you can. Example :

DELETE TableA 
FROM TableA AS a
INNER JOIN TableB AS b
ON a.BId = b.BId
WHERE [filter condition]

answered Jan 13 '09 at 16:50

Diadistis

12,086
1
33
55

10

I prefer to refer to the table in the first line by its alias. That is "Delete a" rather than "Delete TableA". In the case where you join the table with itself, it makes it clear which side you want to delete. – Jeremy Stein Jun 07 '12 at 14:18

score 11 · Answer 4 · answered Jul 22 '11 at 12:55

11

Was trying to do this with an access database and found I needed to use a.* right after the delete.

DELETE a.*
FROM TableA AS a
INNER JOIN TableB AS b
ON a.BId = b.BId
WHERE [filter condition]

answered Jul 22 '11 at 12:55

Tony Emrud

361
5
4

1

From rejected pending edit: "The UniqueRecords property has to be set to yes, otherwise it won't work. (https://support.microsoft.com/kb/240098)" – StuperUser Jan 03 '14 at 15:58

score 9 · Answer 5 · answered Aug 02 '10 at 14:58

9

It's almost the same in MySQL, but you have to use the table alias right after the word "DELETE":

DELETE a
FROM TableA AS a
INNER JOIN TableB AS b
ON a.BId = b.BId
WHERE [filter condition]

answered Aug 02 '10 at 14:58

Michael Butler

6,079
3
38
46

score 3 · Answer 6 · answered Dec 22 '14 at 17:19

I'm using this

DELETE TableA 
FROM TableA a
INNER JOIN
TableB b on b.Bid = a.Bid
AND [condition]

and @TheTXI way is good as enough but I read answers and comments and I found one things must be answered is using condition in WHERE clause or as join condition. So I decided to test it and write an snippet but didn't find a meaningful difference between them. You can see sql script here and important point is that I preferred to write it as commnet because of this is not exact answer but it is large and can't be put in comments, please pardon me.

Declare @TableA  Table
(
  aId INT,
  aName VARCHAR(50),
  bId INT
)
Declare @TableB  Table
(
  bId INT,
  bName VARCHAR(50)  
)

Declare @TableC  Table
(
  cId INT,
  cName VARCHAR(50),
  dId INT
)
Declare @TableD  Table
(
  dId INT,
  dName VARCHAR(50)  
)

DECLARE @StartTime DATETIME;
SELECT @startTime = GETDATE();

DECLARE @i INT;

SET @i = 1;

WHILE @i < 1000000
BEGIN
  INSERT INTO @TableB VALUES(@i, 'nameB:' + CONVERT(VARCHAR, @i))
  INSERT INTO @TableA VALUES(@i+5, 'nameA:' + CONVERT(VARCHAR, @i+5), @i)

  SET @i = @i + 1;
END

SELECT @startTime = GETDATE()

DELETE a
--SELECT *
FROM @TableA a
Inner Join @TableB b
ON  a.BId = b.BId
WHERE a.aName LIKE '%5'

SELECT Duration = DATEDIFF(ms,@StartTime,GETDATE())

SET @i = 1;
WHILE @i < 1000000
BEGIN
  INSERT INTO @TableD VALUES(@i, 'nameB:' + CONVERT(VARCHAR, @i))
  INSERT INTO @TableC VALUES(@i+5, 'nameA:' + CONVERT(VARCHAR, @i+5), @i)

  SET @i = @i + 1;
END

SELECT @startTime = GETDATE()

DELETE c
--SELECT *
FROM @TableC c
Inner Join @TableD d
ON  c.DId = d.DId
AND c.cName LIKE '%5'

SELECT Duration    = DATEDIFF(ms,@StartTime,GETDATE())

If you could get good reason from this script or write another useful, please share. Thanks and hope this help.

score 3 · Answer 7 · answered Nov 19 '11 at 19:14

The syntax above doesn't work in Interbase 2007. Instead, I had to use something like:

DELETE FROM TableA a WHERE [filter condition on TableA] 
  AND (a.BId IN (SELECT a.BId FROM TableB b JOIN TableA a 
                 ON a.BId = b.BId 
                 WHERE [filter condition on TableB]))

(Note Interbase doesn't support the AS keyword for aliases)

score 2 · Answer 8 · answered Jun 13 '12 at 18:36

Let's say you have 2 tables, one with a Master set (eg. Employees) and one with a child set (eg. Dependents) and you're wanting to get rid of all the rows of data in the Dependents table that cannot key up with any rows in the Master table.

delete from Dependents where EmpID in (
select d.EmpID from Employees e 
    right join Dependents d on e.EmpID = d.EmpID
    where e.EmpID is null)

The point to notice here is that you're just collecting an 'array' of EmpIDs from the join first, the using that set of EmpIDs to do a Deletion operation on the Dependents table.

score 2 · Answer 9 · answered Jul 20 '12 at 17:31

In SQLite, the only thing that work is something similar to beauXjames' answer.

It seems to come down to this DELETE FROM table1 WHERE table1.col1 IN (SOME TEMPORARY TABLE); and that some temporary table can be crated by SELECT and JOIN your two table which you can filter this temporary table based on the condition that you want to delete the records in Table1.

score 1 · Answer 10 · edited Mar 02 '21 at 00:39

1

You can run this query:

    DELETE FROM TableA
    FROM
       TableA a, TableB b 
    WHERE
       a.Bid=b.Bid
    AND
       [my filter condition]

edited Mar 02 '21 at 00:39

Druid

6,423
4
41
56

answered Jan 10 '14 at 13:27

Aditya

61
6

score 1 · Answer 11 · edited Feb 22 '16 at 20:42

1

The simpler way is:

DELETE TableA
FROM TableB
WHERE TableA.ID = TableB.ID

edited Feb 22 '16 at 20:42

ByteHamster

4,884
9
38
53

answered Feb 22 '16 at 20:22

Carlos Barini

129
1
4

1c1cle · Answer 12 · 2020-04-24T16:23:26.407

DELETE FROM table1
where id IN 
    (SELECT id FROM table2..INNER JOIN..INNER JOIN WHERE etc)

Minimize use of DML queries with Joins. You should be able to do most of all DML queries with subqueries like above.

In general, joins should only be used when you need to SELECT or GROUP by columns in 2 or more tables. If you're only touching multiple tables to define a population, use subqueries. For DELETE queries, use correlated subquery.

T-SQL: Selecting rows to delete via joins

12 Answers12

Linked

Related