How to Delete from Multiple Tables Using Inner Join in SQL Server

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

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.

Delete from 2 tables using INNER JOIN

You cannot issue a delete statement against more than one table at a time, you need to have individual delete statements for each of the related tables before deleting the parent record(s)

How can I delete using INNER JOIN with SQL Server?

You need to specify what table you are deleting from. Here is a version with an alias:

DELETE w
FROM WorkRecord2 w
INNER JOIN Employee e
  ON EmployeeRun=EmployeeNo
WHERE Company = '1' AND Date = '2013-05-06'

How to delete rows from two tables using INNER JOIN in mysql?

From the MySQL Docs it looks like you can do this easily:

 DELETE t1, t2 
   FROM t1 INNER JOIN t2 INNER JOIN t3
  WHERE t1.id=t2.id AND t2.id=t3.id;

OR

DELETE 
  FROM t1, t2 
 USING t1 INNER JOIN t2 INNER JOIN t3
 WHERE t1.id=t2.id AND t2.id=t3.id;

It also looks like the newer, preferable, JOIN standard is acceptable and I have no idea why your query is complaining. Are you sure you haven't got any strange characters in your query?

Is it possible to delete from multiple tables in the same SQL statement?

Nope, you'd need to run multiple statements.

Because you need to delete from two tables, consider creating a temp table of the matching ids:

SELECT U.Id INTO #RecordsToDelete
FROM Users U
   JOIN LinkingTable J ON U.Id = J.U_Id
...

And then delete from each of the tables:

DELETE FROM Users 
WHERE Id IN (SELECT Id FROM #RecordsToDelete)

DELETE FROM LinkingTable
WHERE Id IN (SELECT Id FROM #RecordsToDelete)

Delete from multiple tables with SqlCommand

Your Problem in the SQL Statement, it is not valid.

You should divide the statement into two ones:

First Delete Comments, then delete Posts

Sequence is Important

var deleteComments = new SqlCommand("DELETE Comments FROM Posts INNER JOIN Comments ON Posts.PostId = Comments.PostId WHERE Posts.PostId = @PostId;");
        deleteComments.Parameters.AddWithValue("@PostId", postId);
        _dataAccess.ExecuteQuery(deleteComments);
        
var deletePosts = new SqlCommand("DELETE Posts WHERE PostId= @PostId;");
        deletePosts.Parameters.AddWithValue("@PostId", postId);
        _dataAccess.ExecuteQuery(deletePosts);

The other option, using one statement:

var delete = new SqlCommand("DELETE Comments FROM Posts INNER JOIN Comments ON Posts.PostId = Comments.PostId WHERE Posts.PostId = @PostId; DELETE Posts WHERE PostId= @PostId;");
            delete.Parameters.AddWithValue("@PostId", postId);
            _dataAccess.ExecuteQuery(delete);

More Explanation:

Using the Following Prepared SQL Script using SQL Studio (SSMS):

CREATE TABLE Posts (PostId  INT, PostText varchar(20))
CREATE TABLE Comments (CommentId INT, PostId INT, CommentText varchar(20))

INSERT INTO Posts VALUES (1, 'text')
INSERT INTO Comments VALUES (1,1, 'comment here')

when I run your DELETE statement

DELETE Posts, Comments FROM Posts INNER JOIN Comments ON Posts.PostId = Comments.PostId WHERE Posts.PostId = 1

It gives me the same error

When I run

DELETE Comments FROM Posts INNER JOIN Comments ON Posts.PostId = Comments.PostId WHERE Posts.PostId = 1;

DELETE Posts WHERE PostId = 1;

It works fine.

So the rule of thumb in such cases is to use SSMS (MS SQL Studio) to test your SQL statement first and then implement it in C#.

Delete from multiple tables in one statement

Yes, changing the join on table2 to left join would do what you want. Rows in table1 that belong to the list and to table3 will all get deleted, regardless whether they also exist in table2. In the meantime, possible matching rows in table2 will be deleted too.

delete t1, t2
from table1 t1
left join table2 t2 on t1.id = t2.table1_id
inner join table3 t3 on t1.id = t3.table1_id
where t1.id in (?, ?, ?, ?);

I would suggest rewriting the join on table3 to an exists condition instead. This makes the intent of the query more obvious, and might perform better, especially if you have an index on table3(table1_id):

delete t1, t2
from table1 t1
left join table2 t2 on t1.id = t2.table1_id
where 
    t1.id in (?, ?, ?, ?)
    and exists (select 1 from table3 t3 where t3.table1_id = t1.id)

How to delete from 2 tables using join query in SQL Server

I actually like Squirrel's temp approach (+1). It allows for a more complex selection criteria.

That said, if #temp tables are "off-the-table", you can do the following:

Delete A
 From  Student A
 Join  Student_grade B on (A.Student_ID=B.Student_ID)
 Where B.Grade='Fail';

Delete From Student_grade Where Grade='Fail';

---

Related Topics