Delete from multiple tables with SqlCommand

Question

When I try to delete a whole row from 2 tables with my INNER JOIN I get the error shown at the bottom. I have searched on the internet and could not find the problem so I came here for help.

Here is the code:

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

I am getting an error message:

System.Data.SqlClient.SqlException: 'Incorrect syntax near ','.'

something like this: [this](https://stackoverflow.com/questions/783726/how-do-i-delete-from-multiple-tables-using-inner-join-in-sql-server) or [this](https://stackoverflow.com/a/16481475) — Pirate, Nov 30 '20 at 16:05
Does this answer your question? [How do I delete from multiple tables using INNER JOIN in SQL server](https://stackoverflow.com/questions/783726/how-do-i-delete-from-multiple-tables-using-inner-join-in-sql-server) — astentx, Nov 30 '20 at 16:13

Useme Alehosaini · Accepted Answer · 2020-12-09T11:00:47.770

3

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

edited Dec 09 '20 at 11:00

answered Nov 30 '20 at 16:03

Useme Alehosaini

2,998
6
18
26

this works but my stakeholder wants as less queries as possible. and in this it would make 2 separate connections to the database. Is there maybe a way I could fit it in one statement? – Jaap Smit Nov 30 '20 at 16:09
So just merge them – Useme Alehosaini Nov 30 '20 at 16:09
@U8080 you don't need INNER JOIN when you are deleting in this case because you already have PostId in that table. INNER JOIN will be additional overhead. – Emin Mesic Nov 30 '20 at 16:10
@JaapSmit The answer is in the comment just after your question. – astentx Nov 30 '20 at 16:12
Hi @Jaap Smit, the solution is updated with one statement as well – Useme Alehosaini Nov 30 '20 at 16:13
1

next time I will first try the sql statements on SSMS now i think about it its indeed a better way the handle these situations. – Jaap Smit Nov 30 '20 at 16:13
```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);``` – Useme Alehosaini Nov 30 '20 at 16:13
@JaapSmit It doesn't really create a new connection. If you operate on the same SqlConnection object, that's still a single connection. And even if you're using multiple SqlConnection objects, there's still connection pooling. So don't worry about using too many connections for this: just write easy to understand and maintain code that performs well. – mason Nov 30 '20 at 17:40

score 0 · Answer 2 · answered Nov 30 '20 at 16:06

There is no option in MSSQL Server to remove data from multiple tables in one statement. So, your statement DELETE Posts, Comments FROM is incorrect.

You have two options to set this foreign key to be CASCADE DELETE or to rewrite your statement for deleting data in these two tables like the following one:

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

score 0 · Answer 3 · answered Nov 30 '20 at 19:51

Your MS SQL query is incorrect. If you want to delete from mutiple tables, you have two choices:

setup cascading delete where a delete in one table will automatically cause deletes in dependent tables
write separate delete queries such as:

DELETE FROM Comments WHERE PostId = @PostId
DELETE FROM Posts WHERE PostId = @PostId

However, I suggest that you test your queries through the SQL Management Studio first before trying to develop code for them.

Delete from multiple tables with SqlCommand

3 Answers3