CODEWITHSUNDEEP
mysqlsqlstored-proceduresmysql-error-1054
Engrost
Engrost

Reputation: 799

MySQL INSERT INTO ... SELECT #1054 ERROR

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

Upvotes: 0

Views: 1401

Answers (3)

pavlovt
pavlovt

Reputation: 51

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

Upvotes: 0

Justin Giboney
Justin Giboney

Reputation: 3301

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

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300769

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

Upvotes: 1

Related Questions