MySQL INSERT INTO ... SELECT #1054 ERROR

Question

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

Answer 1

Presumably this is due to the rename: SELECT p.product_sku sku. A column name of product_sku is expected.

Answer 2

Does this part of your query work by it self?

SELECT p.product_sku sku, get_cheaper(p.product_sku)cheaper_sku
FROM jos_vm_product p;

Edit:

Since that works, try creating a view from it.

CREATE VIEW products_similar 
AS SELECT p.product_sku sku, get_cheaper(p.product_sku)cheaper_sku
FROM jos_vm_product p;

You will be able to query the view just like a normal table