MySQL 插入...选择 #1054 错误
任何人都可以帮忙吗..
SELECT 自己可以工作，
INSERT 可以单独使用 VALUES 也可以工作。
注意：
products_similar has 2 columns VARCHARS
get_cheaper() - stored routine returning VARCHAR
我从来没有遇到过 INSERT INTO ... SELECT 问题。
但是当我将它们组合起来时，出现以下错误：
SQL 查询：文档
INSERT INTO `products_similar` (
`product_sku` ,
`better_priced_sku`
)
SELECT p.product_sku sku, get_cheaper(
p.product_sku
)cheaper_sku
FROM jos_vm_product p;
MySQL 说：文档
字段列表”中的未知列“product_sku”它有效：
#1054 -当我注释掉 get_cheaper(p.product_sku) Cheap_sku 时，“
TRUNCATE TABLE `products_similar` ;# MySQL returned an empty result set (i.e. zero rows).
INSERT INTO `products_similar` (
`product_sku` ,
`better_priced_sku`
)
SELECT p.product_sku sku, p.product_sku sku# , get_cheaper(p.product_sku) cheaper_sku
FROM jos_vm_product p;# Affected rows: 43882
Can anybody please help..
SELECT on its own works
INSERT on its own with VALUES works also.
note:
products_similar has 2 columns VARCHARS
get_cheaper() - stored routine returning VARCHAR
I never ever had problems with INSERT INTO ... SELECT.
But when I combine them I get error below:
SQL query: Documentation
INSERT INTO `products_similar` (
`product_sku` ,
`better_priced_sku`
)
SELECT p.product_sku sku, get_cheaper(
p.product_sku
)cheaper_sku
FROM jos_vm_product p;
MySQL said: Documentation
#1054 - Unknown column 'product_sku' in 'field list'
when I comment out get_cheaper(p.product_sku) cheaper_sku it works:
TRUNCATE TABLE `products_similar` ;# MySQL returned an empty result set (i.e. zero rows).
INSERT INTO `products_similar` (
`product_sku` ,
`better_priced_sku`
)
SELECT p.product_sku sku, p.product_sku sku# , get_cheaper(p.product_sku) cheaper_sku
FROM jos_vm_product p;# Affected rows: 43882
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
据推测，这是由于重命名所致：
SELECT p.product_sku sku。 预期的列名称为product_sku。Presumably this is due to the rename:
SELECT p.product_sku sku. A column name of product_sku is expected.您的查询的这一部分可以单独工作吗？
编辑：
既然可行，请尝试从中创建一个视图。
您将能够像普通表一样查询视图
Does this part of your query work by it self?
Edit:
Since that works, try creating a view from it.
You will be able to query the view just like a normal table
这是mysql手册对此问题的答案：
以下列定义演示了每种可能性：
自动初始化和自动更新：
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
仅自动初始化：
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
仅自动更新：
ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
两者都不是：
ts TIMESTAMP DEFAULT 0
您对最后一个感兴趣 - 如果您将时间戳的默认值设置为 0，它将不会自动更新
Here is the answer to this question from mysql manual:
The following column definitions demonstrate each possibility:
Auto-initialization and auto-update:
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
Auto-initialization only:
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
Auto-update only:
ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
Neither:
ts TIMESTAMP DEFAULT 0
You are interested in the last one - if you set the default value of the timestamp to 0 it will not be automaticly updated