如何在 UPDATE 语句中使用 JOIN?

How can I use JOIN in UPDATE statement?(如何在 UPDATE 语句中使用 JOIN?)

问题描述

我有一张这样的桌子:

//QandA+----+----------------------------------------+---------+----------+------------+|身份证 |身体 |相关 |接受 |作者_id |+----+----------------------------------------+---------+----------+------------+|1 |问题1 |空 |空 |12345 ||2 |问题1的第一个答案的内容|1 |0 |53456 ||3 |问题2的内容|空 |空 |43634 ||4 |问题1第二个答案的内容|1 |0 |43665 ||5 |问题2第一个答案的内容|3 |1 |43324 |+----+----------------------------------------+---------+----------+------------+/* 相关列:实际上这只是为了回答，这个列包含 id它的问题.(问题总是空的)*//*接受的专栏:实际上这只是为了回答和具体接受的答案.0 表示不接受答案，1 表示接受答案.(问题总是空的)*/

<小时>

在为问题设置可接受的答案之前，我正在尝试实现这个条件:

条件:验证当前用户是否为OP.author_id of 其问题应与 $_SESSION['id'] 相同.

<小时>

这是我的查询:(我拥有的所有数据只是接受答案的 id :answer_id)

UPDATE QandA q CROSS JOIN (SELECT related FROM QandA WHERE id = :answer_id) xSET 接受 = ( id = :answer_id ) - 这就像一个 if 语句WHERE q.related = x.related和-- 验证 OP(从 QandA 中选择 1WHERE id = x.related ANDauthor_id = $_SESSION['id'])

<块引用>

#1093 - 您不能在 FROM 子句中指定目标表 'tbname' 进行更新

我该如何解决?

<小时>

其实还有一个条件:

+----+----------------------------+---------+----------+------------+------+|身份证 |身体 |相关 |接受 |作者_id |免费|+----+----------------------------------------+---------+----------+------------+------+|1 |问题1 |空 |空 |12345 |空 ||2 |问题1的第一个答案的内容|1 |0 |53456 |空 ||3 |问题2的内容|空 |空 |43634 |300 ||4 |问题1第二个答案的内容|1 |0 |43665 |空 ||5 |问题2第一个答案的内容|3 |1 |43324 |空 |+----+----------------------------------------+---------+----------+------------+------+/* 免费专栏:实际上这只是为了提问.`null` 表示这是一个免费问题任何其他数字都意味着它不是.(答案总是空")*/

附加条件:如果问题是免费的，那么 OP 可以接受它的答案，并更改他接受的答案，并撤消他接受的答案.但如果问题不是免费的，那么 OP 只能接受一次问题，他不能撤消它，也不能更改接受的答案.这是在 MySQL 中实现该条件:

(从 QandA 中选择 1WHERE id = x.related AND((免费不为空并且不在(从 QandA 中选择 1WHERE 相关 = x.related AND接受 = 1)) 或空闲为空))

我觉得应该这样做:

UPDATE QandA AS ans1加入 QandA AS ans2 ON ans2.related = ans1.related在 ans2.related = ques.id 上加入 QandA 作为 quesSET ans1.accepted = (ans1.id = :answer_id)WHERE ques.author_id = :session_idAND ans2.id = :answer_id

第一个 JOIN 过滤到与被接受的答案相同的问题的答案.

第二个 JOIN 找到那个问题.

WHERE 子句将只更新给定作者的问题，并指定接受的答案 ID.

演示

对于附加条件，添加

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

到 WHERE 子句.ques.free IS NULL 匹配任何免费问题，ans1.accepted IS NULL 匹配没有接受答案的问题(因为当一个答案被接受时，所有其他答案该问题得到 accepted = 0).

没有接受答案的问题演示
免费问题演示

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

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

这篇关于如何在 UPDATE 语句中使用 JOIN?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！