MYSQL:子查询到主查询中更新的表

Question

我想做这样的事情:

1. 在 TAGS 表中找到 name='someName' 的一行，记住它的 id
2. 在同一张表中找到带有 someCondition 的另一行，并在该行中设置 col reference=上面的 id

尝试使用子查询执行此操作，但 mysql 拒绝说我不能子查询我在主查询中更新的表。

我怎样才能实现上述想法？

谢谢

Answer 1

Convert your subquery to a join然后 UPDATE:

You can also perform UPDATE operations covering multiple tables. However, you cannot use ORDER BY or LIMIT with a multiple-table UPDATE. The table_references clause lists the tables involved in the join. Its syntax is described in Section 12.2.8.1, “JOIN Syntax”. Here is an example:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;
The preceding example shows an inner join that uses the comma operator, but multiple-table 

UPDATE statements can use any type of join permitted in SELECT statements, such as LEFT JOIN.