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 ','.'

Does this answer your question? How do I delete from multiple tables using INNER JOIN in SQL server — astentx
– astentx, Commented Nov 30, 2020 at 16:13

Useme Alehosaini · Accepted Answer · 2020-12-09 11:00:47Z

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

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?
@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.
@JaapSmit The answer is in the comment just after your question.
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.

Emin Mesic · Accepted Answer · 2020-11-30 16:06:46Z

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

user19754 · Accepted Answer · 2020-11-30 19:51:23Z

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.

Collectives™ on Stack Overflow

Delete from multiple tables with SqlCommand

3 Answers 3

8 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Linked

Related