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

User · Answer

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

User · Answer

you can run this query -  Delete from TableA  from  TableA a  TableB b  where a Bid b Bid AND  my filter condition

User · Answer

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

User · Answer

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  lt  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  lt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I would use this syntax  Delete a  from TableA a Inner Join TableB b on  a BId   b BId WHERE  filter condition

User · Answer

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

User · Answer

you can run this query -  Delete from TableA  from  TableA a  TableB b  where a Bid b Bid AND  my filter condition

User · Answer

DELETE TableA FROM   TableA a        INNER JOIN TableB b                ON b Bid   a Bid                   AND  my filter condition     should work

User · Answer

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

User · Answer

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

User · Answer

The simpler way is   DELETE TableA FROM TableB WHERE TableA ID   TableB ID

User · Answer

Yes you can  Example    DELETE TableA  FROM TableA AS a INNER JOIN TableB AS b ON a BId   b BId WHERE  filter condition

User · Answer

I would use this syntax  Delete a  from TableA a Inner Join TableB b on  a BId   b BId WHERE  filter condition

User · Answer

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

User · Answer

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

User · Answer

I would use this syntax  Delete a  from TableA a Inner Join TableB b on  a BId   b BId WHERE  filter condition

User · Answer

The simpler way is   DELETE TableA FROM TableB WHERE TableA ID   TableB ID

User · Answer

DELETE TableA FROM   TableA a        INNER JOIN TableB b                ON b Bid   a Bid                   AND  my filter condition     should work

User · Answer

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

User · Answer

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  lt  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  lt  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

User · Answer

DELETE TableA FROM   TableA a        INNER JOIN TableB b                ON b Bid   a Bid                   AND  my filter condition     should work

User · Answer

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

User · Answer

Yes you can  Example    DELETE TableA  FROM TableA AS a INNER JOIN TableB AS b ON a BId   b BId WHERE  filter condition

User · Answer

Yes you can  Example    DELETE TableA  FROM TableA AS a INNER JOIN TableB AS b ON a BId   b BId WHERE  filter condition

User · Answer

DELETE TableA FROM   TableA a        INNER JOIN TableB b                ON b Bid   a Bid                   AND  my filter condition     should work

User · Answer

I would use this syntax  Delete a  from TableA a Inner Join TableB b on  a BId   b BId WHERE  filter condition

User · Answer

Yes you can  Example    DELETE TableA  FROM TableA AS a INNER JOIN TableB AS b ON a BId   b BId WHERE  filter condition

[tsql] T-SQL: Selecting rows to delete via joins

Examples related to tsql

Examples related to join