c# - 使用 SqlCommand 从多个表中删除

Question

当我尝试使用 INNER JOIN 从 2 个表中删除整行时，出现底部显示的错误。我在网上搜索过，没有找到问题，所以我来这里寻求帮助。

代码如下:

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

更多解释:

通过 SQL Studio (SSMS) 使用以下准备好的 SQL 脚本:

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 语句时

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;

它工作正常。

因此经验法则在这种情况下是使用SSMS (MS SQL Studio)先测试你的SQL语句然后执行它在 C# 中。