重复密钥更新+子查询上的MySQL [英] Mysql on duplicate key update + sub query

问题描述

使用此问题的答案:需要MySQL INSERT-SELECT查询具有数百万条记录的表

new_table
    * date
    * record_id (pk)
    * data_field


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

我需要与小组成员一起工作并加入..以便进行

I need this to work with a group by and join.. so to edit:

INSERT INTO new_table (date,record_id,data_field,value)
    SELECT date, record_id, data_field, 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，则会显示字段列表中未定义的错误.

I can't seem to get the value updated. If I specify old_table.value I get a not defined in field list error.

推荐答案

每个文档位于 http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

在ON DUPLICATE KEY UPDATE的values部分中，只要不在SELECT部分​​中使用GROUP BY，就可以引用其他表中的列.副作用是您必须在值部分中限定非唯一列名.

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语句.您需要改用此技巧.基本上，这将创建一个派生表供您查询.可能效率不是很高，但是可以.

So, you cannot use the select query because it has a group by statement. You need to use this trick instead. Basically, this creates a derived table for you to query from. It may not be incredibly efficient, but it works.

INSERT INTO new_table (date,record_id,data_field,value)
    SELECT date, record_id, data_field, value 
    FROM (
        SELECT date, record_id, data_field, 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;

这篇关于重复密钥更新+子查询上的MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！