通过 UPDATE 和 INSERT INTO 将数据从一个表复制到另一个表时出现问题

发布于 2024-11-17 10:39:04 字数 1293 浏览 2 评论 0原文

我有两个表，需要将数据从一个表移动到另一个表。我只想将特定值从第一列（即，如果它们 = ni (varchar)）移动到第二列中的新列。

所以我想从表lim_survey_56999中选择下面的nr数据

`id` `56999X159X3400`
1    2
2    6                  
3    nr
4    mi

并将其移动到新表lim_survey_56999_cube中，这样它就显示

`id`  `External-Unique-Factors4NR`
1     NULL
2     NULL                  
3     nr
4     NULL

我不能使用INSERT INTO，因为lim_survey_56999_cube中已经有数据，我需要它来匹配表中的原始行第一张表。

下面是我的尝试及其结果/错误消息

UPDATE `lime_survey_56999_cube` set `lime_survey_56999_cube`.`External-Unique-Factors4NR` = (select `56999X159X3400` from `lime_survey_56999` where `56999X159X3400` = 'nr')

错误#1242 - 子查询返回超过 1 行

UPDATE `lime_survey_56999_cube` 
SET `External-Unique-Factors4NR` = 'nr'
WHERE `56999X159X3400` in (select `56999X159X3400` from `lime_survey_56999` where `56999X159X3400` = 'nr');

错误#1054 - 'IN/ALL/ANY 子查询'中的未知列'56999X159X3400'

UPDATE `lime_survey_56999_cube` 
INNER JOIN `lime_survey_56999` 
SET `lime_survey_56999_cube`.`External-Unique-Factors4NR` = `lime_survey_56999`.`56999X159X3400` 
WHERE `lime_survey_56999`.`56999X159X3400` = 'nr'

这只会将 nr 放入外部唯一的所有列中-Factors4NR，而不是它对应于第一个表中的条目。

任何帮助将非常感激！我已经为此抓狂了！

原文

I have two tables and need to move data from one to the other. I just want to move specific values from the first column, i.e. if they = ni (varchar) to a new column in the second.

So I'd like to select the nr data below from table lime_survey_56999

`id` `56999X159X3400`
1    2
2    6                  
3    nr
4    mi

And move it into the new table lime_survey_56999_cube so it reads

`id`  `External-Unique-Factors4NR`
1     NULL
2     NULL                  
3     nr
4     NULL

I can't use INSERT INTO because there is data already in lime_survey_56999_cube and I need this to match the original row in the first table.

Below are my attempts and their outcomes/error messages

UPDATE `lime_survey_56999_cube` set `lime_survey_56999_cube`.`External-Unique-Factors4NR` = (select `56999X159X3400` from `lime_survey_56999` where `56999X159X3400` = 'nr')

Error #1242 - Subquery returns more than 1 row

UPDATE `lime_survey_56999_cube` 
SET `External-Unique-Factors4NR` = 'nr'
WHERE `56999X159X3400` in (select `56999X159X3400` from `lime_survey_56999` where `56999X159X3400` = 'nr');

Error #1054 - Unknown column '56999X159X3400' in 'IN/ALL/ANY subquery'

UPDATE `lime_survey_56999_cube` 
INNER JOIN `lime_survey_56999` 
SET `lime_survey_56999_cube`.`External-Unique-Factors4NR` = `lime_survey_56999`.`56999X159X3400` 
WHERE `lime_survey_56999`.`56999X159X3400` = 'nr'

This only puts nr into all the columns of External-Unique-Factors4NR, rather than where it corresponds to the entry in the first table.

Any help would be very much appreciated! I've been tearing my hair out over this!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

单身狗的梦 2024-11-24 10:39:04

您缺少连接的 ON 子句，

UPDATE `lime_survey_56999_cube`
INNER JOIN `lime_survey_56999` 
ON `lime_survey_56999_cube`.???? = `lime_survey_56999`.????
SET `lime_survey_56999_cube`.`External-Unique-Factors4NR` = `lime_survey_56999`.`56999X159X3400` 
WHERE `lime_survey_56999`.`56999X159X3400` = 'nr'

将 ???? 替换为相应的列名称

you are missing the ON clause for your join

UPDATE `lime_survey_56999_cube`
INNER JOIN `lime_survey_56999` 
ON `lime_survey_56999_cube`.???? = `lime_survey_56999`.????
SET `lime_survey_56999_cube`.`External-Unique-Factors4NR` = `lime_survey_56999`.`56999X159X3400` 
WHERE `lime_survey_56999`.`56999X159X3400` = 'nr'

replace ???? with the corresponding column names

回复收藏 0 原文

~没有更多了~