Menu
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!
655
It is totally possible to use JOIN and multiple tables in the DELETE statement.
DELETE JOIN is an advanced structured query language(SQL) statement that is used to perform delete operations in multiple tables while using SQL JOIN such that all rows are deleted from the first table and the matching rows in another table or based on the kind of join operation used in the query.
The basic syntax for Delete Join in SQL Server is as follows: The basic syntax for Delete Join in MySQL is as follows: DELETE t1.* The different parameters used in the syntax are: DELETE t1: It is used to delete the required table from the database. Here, you may choose from the first table’s instance t1 and the second table’s instance t2.
Using the same concept of Inner join, we can delete rows from one table based on another table using Inner Join. DELETE T2 FROM Table2 as T2 INNER JOIN Table1 as T1 ON T1. Id = T1 .Id; To simplify syntax, T2 is an alias name for Table2, whose rows we want to delete based on matching rows with Table1.
It is basically a combination of DELETE and JOIN statements. In this article, we will be learning about four types of DELETE operations, namely, DELETE while using INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN.
DELETE t1: It is used to delete the required table from the database. Here, you may choose from the first table’s instance t1 and the second table’s instance t2. FROM table_name1 as t1 JOIN table_name2 as t2: It is used to specify the source from which data has to be fetched and deleted.
DELETE TableA FROM TableA a INNER JOIN TableB b ON b.Bid = a.Bid AND [my filter condition] should work
71
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
