Mysql on duplicate key update + sub query

Question

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.

score 11 · Answer 1 · edited Apr 11 '16 at 09:36

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;

Thanks Justin. You saved our hide over [here](http://stackoverflow.com/q/38050762) — Drew, Jun 28 '16 at 03:51

score 6 · Answer 2 · edited May 23 '17 at 10:27

6

While searching around some more, I found a related question: "MySQL ON DUPLICATE KEY UPDATE with nullable column in unique key".

The answer is that VALUES() can be used to refer to column "value" in the select sub-query.

edited May 23 '17 at 10:27

Community

1
1

answered May 02 '10 at 22:03

jwzk

177
1
6

1

VALUES() does not work on my mysql version (5.1.32). I had to use the trick in my answer below. I sure do wish VALUES() would work - it's much more elegant. – Justin Noel Aug 25 '11 at 11:41
This is a good answer for MySQL 8+. I found that this works on MySQL 8 but errors on 5.7. – Aurast Oct 05 '20 at 18:52

Mysql on duplicate key update + sub query

2 Answers2

Linked