Tutorial :How do I delete from multiple tables using INNER JOIN in SQL server



Question:

In MySQL you can use the syntax

DELETE t1,t2   FROM table1 AS t1   INNER JOIN table2 t2 ...  INNER JOIN table3 t3 ...  

How do I do the same thing in SQL Server?


Solution:1

You can take advantage of the "deleted" pseudo table in this example. Something like:

begin transaction;       declare @deletedIds table ( id int );       delete t1     output deleted.id into @deletedIds     from table1 t1      join table2 t2        on t2.id = t1.id      join table3 t3        on t3.id = t2.id;       delete t2     from table2 t2      join @deletedIds d        on d.id = t2.id;       delete t3     from table3 t3 ...    commit transaction;  

Obviously you can do an 'output deleted.' on the second delete as well, if you needed something to join on for the third table.

As a side note, you can also do inserted.* on an insert statement, and both inserted.* and deleted.* on an update statement.

EDIT: Also, have you considered adding a trigger on table1 to delete from table2 + 3? You'll be inside of an implicit transaction, and will also have the "inserted." and "deleted." pseudo-tables available.


Solution:2

  1. You can always set up cascading deletes on the relationships of the tables.

  2. You can encapsulate the multiple deletes in one stored procedure.

  3. You can use a transaction to ensure one unit of work.


Solution:3

You can use JOIN syntax in FROM clause in DELETE in SQL Server but you still delete from first table only and it's proprietary Transact-SQL extension which is alternative to sub-query.

From example here:

 -- Transact-SQL extension   DELETE      FROM Sales.SalesPersonQuotaHistory        FROM Sales.SalesPersonQuotaHistory AS spqh INNER JOIN             Sales.SalesPerson AS sp ON spqh.BusinessEntityID = sp.BusinessEntityID      WHERE sp.SalesYTD > 2500000.00;  


Solution:4

Example for delete some records from master table and corresponding records from two detail tables:

BEGIN TRAN      -- create temporary table for deleted IDs    CREATE TABLE #DeleteIds (      Id INT NOT NULL PRIMARY KEY    )      -- save IDs of master table records (you want to delete) to temporary table        INSERT INTO #DeleteIds(Id)    SELECT DISTINCT mt.MasterTableId    FROM MasterTable mt     INNER JOIN ...     WHERE ...        -- delete from first detail table using join syntax    DELETE d    FROM DetailTable_1 D    INNER JOIN #DeleteIds X      ON D.MasterTableId = X.Id        -- delete from second detail table using IN clause      DELETE FROM DetailTable_2    WHERE MasterTableId IN (      SELECT X.Id      FROM #DeleteIds X    )        -- and finally delete from master table    DELETE d    FROM MasterTable D    INNER JOIN #DeleteIds X      ON D.MasterTableId = X.Id      -- do not forget to drop the temp table    DROP TABLE #DeleteIds    COMMIT  


Solution:5

Just wondering.. is that really possible in MySQL? it will delete t1 and t2? or I just misunderstood the question.

But if you just want to delete table1 with multiple join conditions, just don't alias the table you want to delete

this:

DELETE t1,t2   FROM table1 AS t1   INNER JOIN table2 t2 ...  INNER JOIN table3 t3 ...  

should be written like this to work in MSSQL:

DELETE table1  FROM table1   INNER JOIN table2 t2 ...  INNER JOIN table3 t3 ...  

to contrast how the other two common RDBMS do a delete operation:

http://mssql-to-postgresql.blogspot.com/2007/12/deleting-duplicates-in-postgresql-ms.html


Solution:6

Basically, no you have to make three delete statements in a transaction, children first and then parents. Setting up cascading deletes is a good idea if this is not a one-off thing and its existence won't conflict with any existing trigger setup.


Solution:7

In SQL server there is no way to delete multiple tables using join. So you have to delete from child first before delete form parent.


Solution:8

This is an alternative way of deleting records without leaving orphans.

    Declare @user Table(keyValue int  , someString varchar(10))  insert into @user  values(1,'1 value')    insert into @user  values(2,'2 value')    insert into @user  values(3,'3 value')    Declare @password Table(  keyValue int , details varchar(10))  insert into @password  values(1,'1 Password')  insert into @password  values(2,'2 Password')  insert into @password  values(3,'3 Password')            --before deletion    select * from @password a inner join @user b                  on a.keyvalue = b.keyvalue    select * into #deletedID from @user where keyvalue=1 -- this works like the output example    delete  @user where keyvalue =1    delete @password where keyvalue in (select keyvalue from #deletedid)      --After deletion--    select * from @password a inner join @user b                  on a.keyvalue = b.keyvalue    


Solution:9

All has been pointed out. Just use either DELETE ON CASCADE on the parent table or delete from the child-table to the parent.


Solution:10

As Aaron has already pointed out, you can set delete behaviour to CASCADE and that will delete children records when a parent record is deleted. Unless you want some sort of other magic to happen (in which case points 2, 3 of Aaron's reply would be useful), I don't see why would you need to delete with inner joins.


Solution:11

To build upon John Gibb's answer, for deleting a set of data in two tables with a FK relationship:

--*** To delete from tblMain which JOINs to (has a FK of) tblReferredTo's PK    --       i.e.  ON tblMain.Refer_FK = tblReferredTo.ID  --*** !!! If you're CERTAIN that no other rows anywhere also refer to the   --      specific rows in tblReferredTo !!!  BEGIN TRAN;        --*** Keep the ID's from tblReferredTo when we DELETE from tblMain      DECLARE @tblDeletedRefs TABLE ( ID INT );      --*** DELETE from the referring table first      DELETE FROM tblMain       OUTPUT DELETED.Refer_FK INTO @tblDeletedRefs  -- doesn't matter that this isn't DISTINCT, the following DELETE still works.      WHERE ..... -- be careful if filtering, what if other rows                   --   in tblMain (or elsewhere) also point to the tblReferredTo rows?        --*** Now we can remove the referred to rows, even though tblMain no longer refers to them.      DELETE tblReferredTo      FROM   tblReferredTo INNER JOIN @tblDeletedRefs Removed                ON tblReferredTo.ID = Removed.ID;    COMMIT TRAN;  


Solution:12

DELETE     TABLE1 LIN  FROM TABLE1 LIN  INNER JOIN TABLE2 LCS ON  CONDITION  WHERE CONDITION  


Solution:13

$sql="DELETE FROM basic_tbl,education_tbl, personal_tbl ,address_tbl,department_tbl USING basic_tbl,education_tbl, personal_tbl ,address_tbl,department_tbl WHERE b_id=e_id=p_id=a_id=d_id='".$id."' "; $rs=mysqli_query($con,$sql);


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »