mysql中update语句join,mysql – 如何在UPDATE语句中使用JOIN？

我有这样一张桌子：

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

在设置问题的已接受答案之前,我正在尝试实施此条件：

条件：验证当前用户是否为OP.其问题的author_id应与$_SESSION [‘id’]相同.

这是我的查询:(我拥有的所有数据只是接受答案的id：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

我该如何解决？

编辑：实际上还有一个条件：

+----+----------------------------------------+---------+----------+-----------+------+

| 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`) */

附加条件：如果问题是免费的,那么OP可以接受答案,并改变他接受的答案,并撤消他接受的答案.但是如果这个问题不是免费的,那么OP就可以接受一次问题了,他也无法撤消它,他也无法改变接受的答案.这是在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

)

)

解决方法:

我认为应该这样做：

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

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

第二个JOIN发现了这个问题.

WHERE子句将更新仅限于给定作者的问题,并指定接受的答案ID.

对于附加条件,请添加

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

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

标签：sql,mysql,join,sql-update

来源： https://codeday.me/bug/20191003/1848447.html