Mysql when duplicating a key update + additional query

Question

Using the answer from this question: MySQL INSERT required - 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 the group and join .. so 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;

It seems I can not update the value. If I specify old_table.value, I will get an error in the list of fields.

Answer 1

In the docs http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

ON ON DUPLICATE KEY UPDATE , GROUP BY SELECT. , .

, 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;

Answer 2

: "MySQL ON DUPLICATE KEY UPDATE .

: VALUES() select.