Reputation: 53861
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?
Upvotes: 134
Views: 285926
Reputation: 183
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.
Upvotes: 5
Reputation: 21
All has been pointed out. Just use either DELETE ON CASCADE
on the parent table
or delete from the child-table
and then parent
.
Upvotes: 2
Reputation: 10773
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.
Upvotes: 136
Reputation: 6075
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;
Upvotes: 1
Reputation: 19793
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;
Upvotes: 16
Reputation: 1
$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);
Upvotes: -6
Reputation: 7
DELETE TABLE1 LIN
FROM TABLE1 LIN
INNER JOIN TABLE2 LCS ON CONDITION
WHERE CONDITION
Upvotes: -4
Reputation: 3236
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
Upvotes: 2
Reputation: 14607
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
Upvotes: 15
Reputation: 9664
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.
Upvotes: 15
Reputation: 39393
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
Upvotes: 9
Reputation: 91881
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.
Upvotes: 9
Reputation: 20782
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.
Upvotes: 1