Error 1062 in mysql

Question

INSERT INTO order SELECT * FROM order WHERE date > DATE_SUB(NOW(), INTERVAL 6 MONTH);

I am getting Error Code: 1062 Duplicate entry '5890738' for key 'PRIMARY'

How to resolve this as I want only 6 months data but this table contains 6 years data?

Answer 1

What you get as error means that you try to insert id (which a primary and unique key) twice times, to solve that you must use something like that :

INSERT INTO order(all your columns except Id_column) SELECT all your columns except Id_column FROM order WHERE date > DATE_SUB(NOW(), INTERVAL 6 MONTH);

Note : Id must be auto-increment column

Answer 2

Use MySQL query:

INSERT INTO order(all your columns except Id_column)   
SELECT all your columns except Id_column   
FROM order WHERE date > DATE_SUB(NOW(), INTERVAL 6 MONTH);