How can I use JOIN in UPDATE statement?

2019-01-28 23:36发布

问题:

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
        )
)

回答1:

I think this should do it:

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

The first JOIN filters down to the answers to the same question as the answer being accepted.

The second JOIN finds that question.

The WHERE clause will restrict the update only to questions with the given author and specifies the answer ID being accepted.

DEMO

For the additional condition, add

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

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).

DEMO of question with no accepted answer
DEMO of question that's free