Shafizadeh Shafizadeh - 6 months ago 11
SQL Question

How can I use JOIN in UPDATE statement?

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

Answer

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 to the WHERE clause.

Comments