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

User · Answer

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

User · Answer

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

User · Answer

You can always set up cascading deletes on the relationships of the tables  You can encapsulate the multiple deletes in one stored procedure  You can use a transaction to ensure one unit of work

User · Answer

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  gt  2500000 00

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] How do I delete from multiple tables using INNER JOIN in SQL server

Examples related to sql

Examples related to sql-server

Examples related to tsql