mysql:使用子查询更新， [英] mysql: update with subquery,

问题描述

我有一个带有select语句的更新查询，该查询单独起作用.它将它用于无法正常工作的更新.

I have an update query with a select statement, which separately works. It's using it for the update that's not working.

update data set data.id = (select nid from node inner join data on node.title = data.name);

我收到错误

您无法在FROM子句中指定要更新的目标表'data'"

"You can't specify target table 'data' for update in FROM clause"

因此，在深入研究之后，我发现我可以编写包括另一个select语句的内容:

So, after digging around, I found that I could write include another select statement:

update data set data.id = (select nid from(select nid from node inner join data on node.title = data.name) AS temptable);

我收到错误

子查询返回多于1行"

"Subquery returns more than 1 row "

因此，在进一步挖掘之后，我添加了一个"ANY"，因为这是常见的建议:

So after more digging, I added an "ANY", as this is the common recommendation:

update data set data.id = (select nid from ANY (select nid from node inner join data on node.title = data.name) AS temptable);

并获得

您的SQL语法有误；请查看手册对应于您的MySQL服务器版本以使用正确的语法在'((从node.title = biblio_上的节点内部联接数据中选择nid)附近在第1行"

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select nid from node inner join data on node.title = biblio_' at line 1 "

我想念什么?

推荐答案

如果要更新 data 表中的所有行，则可以执行以下操作:

If you want to update all rows in the data table, you can do something like this:

UPDATE data
  LEFT
  JOIN node
    ON node.title = data.name
   SET data.id = node.nid

注意:

如果 node 中有多行具有相同的 title 值，则该行与 data name 相匹配>，则不确定要从哪个行中分配 nid 的值.

If there are multiple rows in node with the same value for title, which matches a name in data, it's indeterminate which of those rows the value of nid will be assigned from.

如果 data 表中存在 name 值，而在 node 表中(在 title 列)，则会将NULL值分配给 id 列.

If there are values of name in the data table which are not found in the node table (in the title column), then a NULL value will be assigned to the id column.

对查询的一些调整可以修改此行为.

Some tweaks to the query can modify this behavior.

可以使用子查询来完成此操作，但是我只使用联接操作.我认为您可以使用相关的子查询，如下所示:

It's possible to accomplish this using a subquery, but I would just use a join operation. I think you could use a correlated subquery, like this:

UPDATE data
   SET data.id = ( SELECT node.nid
                     FROM node
                    WHERE node.title = data.name
                    ORDER BY node.nid
                    LIMIT 1
                 )

这篇关于mysql:使用子查询更新，的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！