I have a table like this:
// 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) */
I'm trying to implement this condition before setting the accepted answer for a question:
Condition: Validating whether current user is OP or not. author_id of
its question should be the same as $_SESSION['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 - You can't specify target table 'tbname' for update in FROM clause
How can I fix it?
EDIT: 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`) */
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
)
)