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:
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?