i got 2 tables that i want to combine its data. the id is my key field (incremental and distinct). table 1 and table 2 field description for example: id - name - value
i want to insert all of table 2 data into table 1, they have different data but in some rows the same id.
so when i try to: INSERT INTO ladatabase.table2 SELECT * from ladatabase.table1;
i get duplicate entry error.
please help me to suggest how can i solve it and combine the data with different id values for the new data.
Alex.
here are 2 ways:
first you can use if the id AUTO INCREMENT
INSERT INTO ladatabase.table2
SELECT '', name, value from ladatabase.table1;
or you find the first free ID and add it
INSERT INTO ladatabase.table2
SELECT id+555 , name, value from ladatabase.table1;
No insert all fields (exclude 'id'-field from SELECT):
INSERT INTO ladatabase.table2 SELECT name,value from ladatabase.table1;
You need to copy all but the ID column, and it will assign new IDs. You need to list all the other columns explicitly. Leaving out the ID column will cause it to get its default value, which are sequential IDs for an auto_increment field.
INSERT INTO table2 (col2, col3, col4, ...)
SELECT col2, col3, col4, ...
FROM table1
