如何在UPDATE语句中使用JOIN？

  • Z时代
  • 2024-01-10
  • 分类：问答

我有一个这样的表：

// QandA
+----+----------------------------------------+---------+----------+-----------+
| Id |                   body                 | related | accepted | author_id |
+----+----------------------------------------+---------+----------+-----------+
| 1  | content of question1                   | null    | null     | 12345     |
| 2  | content of first answer for question1  | 1       | 0        | 53456     |
| 3  | content of question2                   | null    | null     | 43634     |
| 4  | content of second answer for question1 | 1       | 0        | 43665     |
| 5  | content of first answer for question2  | 3       | 1        | 43324     |
+----+----------------------------------------+---------+----------+-----------+
/* related column: Actually that's just for answers and this column is containing the id of
   its question. (questions always are null) */
/* accepted column: Actually that's just for answers and specifics accepted answer.
   0 means it isn't accepted answer, 1 means it is accepted answer.
   (questions always are null) */

在设置问题的可接受答案之前，我正在尝试实现此 ：

验证当前用户是否为OP。author_id of问题应该与相同$_SESSION['id']。

这是我的查询：（ 我拥有的所有数据只是接受的答案的ID:answer_id）

UPDATE QandA q CROSS JOIN ( SELECT related FROM QandA WHERE id = :answer_id ) x
    SET accepted = ( id = :answer_id ) -- this acts like a if statement 
  WHERE q.related   = x.related
        AND
        -- validating OP
        (SELECT 1 FROM QandA 
          WHERE id = x.related AND
                author_id = $_SESSION['id']
        )

1093-您无法在FROM子句中指定目标表’tbname’进行更新

我该如何解决？

实际上还有一个条件：

+----+----------------------------------------+---------+----------+-----------+------+
| Id |                   body                 | related | accepted | author_id | free |
+----+----------------------------------------+---------+----------+-----------+------+
| 1  | content of question1                   | null    | null     | 12345     | null |
| 2  | content of first answer for question1  | 1       | 0        | 53456     | null |
| 3  | content of question2                   | null    | null     | 43634     | 300  |
| 4  | content of second answer for question1 | 1       | 0        | 43665     | null |
| 5  | content of first answer for question2  | 3       | 1        | 43324     | null |
+----+----------------------------------------+---------+----------+-----------+------+
/* free column: Actually that's just for questions. `null` means it is a free question
   and any number else means it isn't. (answers always are `null`) */

如果问题是免费的，则OP可以接受该问题的答案，然后更改其接受的答案，然后撤消其接受的答案。但是，如果问题不是免费的，那么OP可以一次接受一个问题，他不能撤消它，也不能更改已接受的答案。这是在MySQL中实现该条件的方法：

(SELECT 1 FROM QandA
  WHERE id = x.related AND
        (
          ( free IS NOT NULL AND
            NOT IN ( SELECT 1 FROM QandA
                      WHERE related = x.related AND
                            accepted = 1 )
          ) OR free IS NULL
        )
)

回答：

我认为应该这样做：

UPDATE QandA AS ans1
JOIN QandA AS ans2 ON ans2.related = ans1.related
JOIN QandA AS ques ON ans2.related = ques.id
SET ans1.accepted = (ans1.id = :answer_id)
WHERE ques.author_id = :session_id
AND ans2.id = :answer_id

第一个JOIN过滤器筛选出与接受的问题相同的问题的答案。

第二个JOIN找到了这个问题。

该WHERE子句将仅将更新限制为给定作者的问题，并指定接受的答案ID。

演示

对于其他条件，请添加

AND (ques.free IS NULL or ans1.accepted IS NULL)

该WHERE条款。ques.free IS NULL匹配任何免费问题，并ans1.accepted IS

NULL匹配没有接受答案的问题（因为当一个答案被接受时，该问题的所有其他答案都获得accepted = 0）。

问题的演示，没有被接受的答案

免费的问题的演示

以上是 如何在UPDATE语句中使用JOIN？ 的全部内容， 来源链接： utcz.com/qa/417504.html

回到顶部