MySQL merge two tables with the same IDs-CodePudding

In table_A I have data from this week, the IDs started from 1 up to 5000.

In table_B I have data from last month, whereas the first let's say 3000 IDs conflict with the ones in table_A, and it's not the same data for all of the IDs (ID 1 in table_A is not the same as ID 1 in table_B for example).

I want to basically merge table_B into table_A but getting an ID conflict since some of the IDs I am trying to import already exists.

Table A

id     timestamp     price
...    ...           ...
518    2021-09-16    20

Table B

id     timestamp     price
518    2021-08-15    10

Is there any clever solution out there for this type of issue?

CodePudding user response：

Leave out the id column when inserting from B to A.

INSERT INTO table_A (col1, col2, col3, ...)
SELECT col1, col2, col3, ...
FROM table_B

The columns listed are all the columns except the id column. I assume this is an AUTO_INCREMENT column, so new IDs will be assigned for them after merging.

Before you ask: No, there's no shortcut to select all columns except id. You have to list them all explicitly.