我有这样一张桌子:
// 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应与$_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 – You can’t specify target table ‘tbname’ for update in FROM clause
我该如何解决?
编辑:实际上还有一个条件:
+----+----------------------------------------+---------+----------+-----------+------+
| 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匹配没有接受答案的问题(因为当接受答案时,该问题的所有其他答案都被接受= 0).
标签:sql,mysql,join,sql-update
来源: https://codeday.me/bug/20191003/1848447.html