使用这个问题的答案:需要 MySQL INSERT - SELECT 查询具有数百万条记录的表 https://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;
我需要这个来与分组一起工作并加入..所以要编辑:
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 我会收到“未在字段列表中定义”错误。