在 INSERT INTO 语句 MySQL 中引用当前行
我正在尝试执行如下所示的 INSERT INTO 语句：
insert into table1 (id, data1, data2) select c.id, c.data1, (select a.id
from table3 a, table2 b where b.id=c.id and a.data3=b.data3) from table2 c;
基本上，这里发生的情况是我正在将一些数据从一个表迁移到另一个表中。但是，在新表中，我想要第三个表中的行的 id，而不是存储在原始表中的字符串。
我觉得我的查询已经接近，但是当我运行它时，mysql 给出了错误：
ERROR 1242 (21000): Subquery returns more than 1 row
我认为问题是 b.id=c.id 但我不明白为什么 c. id 不会是一个特定值。如果我将 c.id 替换为已知的 id，查询将按预期运行。
更一般地说，我需要能够从 select 语句的结果访问我所在的“当前行”，并在内部 select 语句中使用它的 id。
如何使我的内部 select 语句仅引用一行？
这是在 CentOS 上运行的 MySql 5.0.77。
I'm trying to perform an INSERT INTO statement like the following:
insert into table1 (id, data1, data2) select c.id, c.data1, (select a.id
from table3 a, table2 b where b.id=c.id and a.data3=b.data3) from table2 c;
Basically what's happening here is I'm migrating some data from one table into another. However, in the new table I want the id of a row from a third table rather than the String that was stored in the original table.
I feel like my query is close, however when I run it mysql is giving the error:
ERROR 1242 (21000): Subquery returns more than 1 row
I think the problem is b.id=c.id but I don't understand why c.id would not be a specific value. If I replace c.id with a known id, the query functions as expected.
More generally, I need to be able to access the 'current row' that I'm on from the results of my select statement and use it's id in that inner select statement.
How can I make my inner select statement only reference one row?
This is MySql 5.0.77 running on CentOS.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我们无法告诉您为什么您的子查询返回不止一行而没有看到更多的表/数据。进行一些调查并找出哪些记录重复，您应该能够解决您的问题。
如果您不关心重复项并且只想查询正常工作，请在子查询上使用
LIMIT 0,1We can't tell you why your subquery returns more than one row without seeing more of your tables / data. Do some investigating and find which record(s) are duplicated and you should be able to resolve your problem.
If you don't care about duplicates and you just want the query to work, use
LIMIT 0,1on your subquery