使用这个问题的答案：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,data_field FROM old_table
ON DUPLICATE KEY UPDATE date=old_table.data,data_field=old_table.data_field;
我需要这个与group by一起工作并加入..所以编辑：
INSERT INTO new_table (date,data_field,value)
SELECT date,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,我得到一个未定义的字段列表错误.
最佳答案
根据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.
因此,您不能使用select查询,因为它具有group by语句.你需要使用这个技巧.基本上,这会创建一个派生表供您查询.它可能效率不高,但它有效.
INSERT INTO new_table (date,value
FROM (
SELECT date,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;