关于重复密钥更新子查询的Mysql

使用这个问题的答案：Need MySQL INSERT – SELECT query for tables with millions of records

new_table
    * date
    * record_id (pk)
    * data_field


INSERT INTO new_table (date,record_id,data_field)
    SELECT date,data_field FROM old_table
        ON DUPLICATE KEY UPDATE date=old_table.data,data_field=old_table.data_field;

我需要这个与group by一起工作并加入..所以编辑：

INSERT INTO new_table (date,data_field,value)
    SELECT date,SUM(other_table.value) as value FROM old_table JOIN other_table USING(record_id) GROUP BY record_id
        ON DUPLICATE KEY UPDATE date=old_table.data,data_field=old_table.data_field,value = value;

我似乎无法更新值.如果我指定old_table.value,我得到一个未定义的字段列表错误.
最佳答案
根据http://dev.mysql.com/doc/refman/5.0/en/insert-select.html的文档

In the values part of ON DUPLICATE KEY UPDATE,you can refer to columns in other tables,as long as you do not use GROUP BY in the SELECT part. One side effect is that you must qualify nonunique column names in the values part.

因此,您不能使用select查询,因为它具有group by语句.你需要使用这个技巧.基本上,这会创建一个派生表供您查询.它可能效率不高,但它有效.

INSERT INTO new_table (date,value 
    FROM (
        SELECT date,SUM(other_table.value) as value 
        FROM old_table
        JOIN other_table
        USING(record_id)
        GROUP BY record_id
    ) real_query 
ON DUPLICATE KEY
    UPDATE date=real_query.date,data_field=real_query.data_field,value = real_query.value;