Mysql on duplicate key update + sub query

Question

Using the answer from this question: http://stackoverflow.com/questions/662877/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.

Answer 1

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.