I tried to insert one-column data from one table to another table based on a same "id" value. I executed following query and it ran successfully and showed all rows are affected in the message. But when I checked the table, it showed blank value for each row of that column. So, no data was inserted.
我尝试根据相同的“id”值将一列数据从一个表插入到另一个表中。我执行了以下查询并成功运行并显示消息中的所有行都受到影响。但是当我检查表时，它显示该列的每一行的空白值。因此，没有插入数据。
insert into edge_table (code)
select d.code as code from d_k as d inner join edge_table as edge on d.segm_id = edge.segm_id
Then I tried with UPDATE. Executed following query. And it gave an error: "more than one row returned by a subquery used as an expression"
然后我试着UPDATE。执行以下查询。并且它给出了一个错误：“用作表达式的子查询返回了多行”
update edge_table set (code) =
(select d.code as code from d_k as d inner join edge_table as edge on d.segm_id = edge.segm_id)
But when I tried only select, it worked fine.
但当我尝试只选择时，它工作正常。
select d.code as code from d_k as d inner join edge_table as edge on d.segm_id = edge.segm_id
1 个解决方案
#1
1
I suspect that you want this:
我怀疑你想要这个：
update edge_table
set code = d.code
from d_k d
where d.segm_id = edge_table.segm_id ;
This updates the existing values in edge_table that have matches in d_k.
这将更新edge_table中与d_k匹配的现有值。
Note: You have an issue in the data, because there are multiple matches for a given row in edge_table. This will update using an arbitrary matching row.
注意：数据中存在问题，因为edge_table中的给定行有多个匹配项。这将使用任意匹配行进行更新。
#1
1
I suspect that you want this:
我怀疑你想要这个：
update edge_table
set code = d.code
from d_k d
where d.segm_id = edge_table.segm_id ;
This updates the existing values in edge_table that have matches in d_k.
这将更新edge_table中与d_k匹配的现有值。
Note: You have an issue in the data, because there are multiple matches for a given row in edge_table. This will update using an arbitrary matching row.
注意：数据中存在问题，因为edge_table中的给定行有多个匹配项。这将使用任意匹配行进行更新。