Mysql 重复键更新 +子查询

发布于 2024-08-31 18:02:58 字数 868 浏览 4 评论 0原文

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

我需要它与分组和连接一起使用..所以要编辑：

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 我会收到“未在字段列表中定义”错误。

原文

Using the answer from this question: 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, 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;

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

春风十里 2024-09-07 18:02:58

根据 http://dev.mysql.com/doc 的文档/refman/5.0/en/insert-select.html

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

因此，您不能使用 select 查询，因为它有一个 group by 语句。你需要改用这个技巧。基本上，这会创建一个派生表供您查询。它的效率可能不是令人难以置信，但它确实有效。

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;

Per the docs at 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.

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;

回复收藏 0 原文