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

使用这个问题的答案:Need MySQL INSERT SELECT query for tables with millions of records new_table * date * record_id (pk) * data_fieldINSERT INTO new_table (date,record_id,data_field) SELECT date,data_field FROM old_table ON DUPLICATE KEY

使用这个问题的答案: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;

关于作者: dawei

【声明】:石家庄站长网内容转载自互联网,其相关言论仅代表作者个人观点绝非权威,不代表本站立场。如您发现内容存在版权问题,请提交相关链接至邮箱:bqsm@foxmail.com,我们将及时予以处理。

为您推荐