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?

Answer 1

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

begin transaction;

   declare @deletedIds table ( id int );

   delete from t1
   output deleted.id into @deletedIds
   from table1 as t1
    inner join table2 as t2
      on t2.id = t1.id
    inner join table3 as t3
      on t3.id = t2.id;

   delete from t2
   from table2 as t2
    inner join @deletedIds as d
      on d.id = t2.id;

   delete from t3
   from table3 as 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.

Answer 2

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;

Answer 3

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.

Answer 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

Answer 5

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.

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 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.

Answer 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;

Answer 12

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

Answer 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);