[SOLVED] INSERT + SELECT + ON DUPLICATE KEY with column aliases in SELECT clause

INSERT + SELECT + ON DUPLICATE KEY with column aliases in SELECT clause

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?

Solution

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