关于重复密钥更新子查询的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;

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!