I'm hitting a rather surprising roadblock when attempting to chain together INSERT, SELECT and ON DUPLICATE KEY in a query where the SELECT clause has column aliases. For example, consider the following situation:
Tables:
CREATE TABLE source (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
v INT NOT NULL
);
INSERT INTO source (v) VALUES (1), (2), (3);
CREATE TABLE dest (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
v INT NOT NULL
);
Suppose I'm trying to fill dest.v
with the values of POW(source.v,2)
regardless of if values exist in dest
already. Naturally, I tried:
INSERT INTO dest
SELECT id, POW(v, 2) AS p FROM source
ON DUPLICATE KEY UPDATE dest.v=source.p;
However, MySQL insists that source.p doesn't exist:
ERROR 1054 (42S22): Unknown column 'source.p' in 'field list'
Rather inconveniently, I have to resort to using the slower and more cumbersome query:
INSERT INTO dest
SELECT * FROM (
SELECT id, POW(v, 2) AS p FROM source
) s
ON DUPLICATE KEY UPDATE dest.v=s.p;
which differs very little from the original query, but works. Why is this the case?
I always write the query as below
INSERT INTO dest ( id, v)
SELECT id, POW(v, 2) AS p FROM source
ON DUPLICATE KEY UPDATE dest.v=VALUES(v);
VALUES() avoid writing same expression again. Always try to specify the columns name you are inserting, just in case you add a new column to the table in some time future