如何在UPDATE语句中使用JOIN?
我有一个这样的表:
// 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 ans1JOIN 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