求助 1054 - Unknown column '*' in 'where clause'的问题

[cailiming1983]

想从第二张表中取值为第一张表的某些字段赋值，频频报1054错误，找不到字段名称，其中amount_compare，amount_20140630是两张表，ibk_name是两张表都拥有的字符型字段，具体代码如下，请帮忙看看。
数据库是mysql 5.6.19

mysql> INSERT INTO amount_compare (custom_count_20140630,amount_20140630,rate_20140630) select custom_count as custom_count_20140630,amount as amount_20140630,rate as rate_20140630 from amount_20140630 where amount_compare.ibk_name=amount_20140630.ibk_name;
1054 - Unknown column 'amount_compare.ibk_name' in 'where clause'

[ACMAIN_CHM]

楼主到底是要更新还是要插入新记录？！

[benluobo]

没有这种写法 你可以通过临时表或者存储过程 先查询出满足连接关系的结果，再insert

[WWWWA]

INSERT INTO amount_compare (custom_count_20140630,amount_20140630,rate_20140630) select custom_count as custom_count_20140630,amount as amount_20140630,rate as rate_20140630 from amount_20140630,amount_compare where amount_compare.ibk_name=amount_20140630.ibk_name;

[heihei122]

看不明白楼主的意思

[知道就是你]

感觉楼主是在执行一个这样的操作 ：将 amount_compare 与 amount_20140630 的关联结果 插入到 amount_compare 表 ，根据理解意思，完善后的SQL脚本如下 ： INSERT INTO amount_compare (custom_count_20140630,amount_20140630,rate_20140630) select customset.custom_count_20140630, customset.amount_20140630 ,customset.rate_20140630 from ( select custom_count as custom_count_20140630,amount as amount_20140630,rate as rate_20140630 from amount_20140630 where amount_compare.ibk_name=amount_20140630.ibk_name ) as customset;