如何在UPDATE语句中使用JOIN? [英] How can I use JOIN in UPDATE statement?
问题描述
我有一个这样的表:
// 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']相同.
Condition: Validating whether current user is OP or not. author_id of its question should be the same as $_SESSION['id'].
这是我的查询:(我拥有的所有数据只是接受的答案的:answer_id的ID)
Here is my query: (all data I have is just the id of accepted answer :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"进行更新
#1093 - You can't specify target table 'tbname' for update in FROM clause
我该如何解决?
编辑:实际上还有一个条件:
Actually there is one more condition:
+----+----------------------------------------+---------+----------+-----------+------+
| 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中实现该条件的方法:
Additional Condition: If the question is free, then OP can accept an answer for it, and change his accepted answer, and undo his accepted answer. But if the question isn't free, then OP just can accept a question one time, and he cannot undo it, and he cannot change accepted answer. Here is implementing of that condition in 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过滤掉与接受答案相同的问题的答案.
The first JOIN filters down to the answers to the same question as the answer being accepted.
第二个JOIN找到了这个问题.
The second JOIN finds that question.
WHERE子句将仅将更新限制为给定作者的问题，并指定接受的答案ID.
The WHERE clause will restrict the update only to questions with the given author and specifies the answer ID being accepted.
对于其他条件，请添加
AND (ques.free IS NULL or ans1.accepted IS NULL)
到WHERE子句. ques.free IS NULL匹配任何自由问题，而ans1.accepted IS NULL匹配没有接受答案的问题(因为当一个答案被接受时，该问题的所有其他答案都得到accepted = 0).
to the WHERE clause. ques.free IS NULL matches any free question, and ans1.accepted IS NULL matches a question with no accepted answer (because when an answer is accepted, all the other answers to that question get accepted = 0).
这篇关于如何在UPDATE语句中使用JOIN?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
