使用SqlCommand从多个表中删除

Question

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

system.data.sqlclient.sqlexception：“”、“.”附近的语法不正确

Answer 1

您的问题出现在SQL语句中，它是无效的。

您应该将语句分为两个：

首先删除注释，然后删除帖子

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

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

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

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

它给了我同样的错误

当我跑的时候

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

DELETE Posts WHERE PostId = 1;

工作很好。