怎么在 UPDATE 语句中使用 JOIN?

本教程将介绍如何在 UPDATE 语句中使用 JOIN?的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

怎么在 UPDATE 语句中使用 JOIN? 教程 第1张

问题描述

我有一张这样的桌子:

//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'] 相同.

这是我的查询:

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:

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?的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。