SQL operation to copy rows from one table to another when index changes

Question

status description

I have two databases, DB1 and DB2, which have the same table Author with the field Author.ID and Author.AuthorName.

The

Author.ID field of DB1.Author has AUTO_INCRMENT, but DB2.Author does not have AUTO_INCRMENT , because it relies on the correctness of DB1 data.

Both tables have a PRIMARY index on Author.ID and a unique index on Author.AuthorName.

DB2.Author has rows copied from DB1.Author.

Both databases use MariaDB version 10.6.7.

---

question

DB1 The administrator deleted some entries in the DB1.Author table and then reordered the indexes so that there were no gaps in the index numbers. This means they may have:

ID	author name
1	one
2	B
3	C

Then they deleted the line where AuthorName is 'B':

ID	author name
1	one
3	C

They finally updated the index so that it had no gaps (3-C changed to 2-C):

ID	author name
1	one
2	C

Now I need to find a way to copy the updated status of a row from DB1.Author to DB2.Author without deleting DB2.Author Everything in the table so that I don't lose data for the CASCADE effect.

What's the best way?

---

My lens

That's what I did, but it obviously doesn't work because in case of a duplicate key it tries to create another duplicate key (duplicate ID 2 will try to insert ' Duplicate value for C' because it already exists on ID3):

INSERT INTO DB2.Author (ID, AuthorName)
SELECT DB1.Author.ID, DB1.Author.AuthorName FROM DB1.Author
ON DUPLICATE KEY UPDATE
    ID = DB1.Author.ID,
    AuthorName = DB1.Author.AuthorName;

---

Other methods?

Besides the possible SQL query solution, is there any other way to automatically update table data in one database when the data is changed in another database? Only some tables need to be copied, not other linked tables.

Answer 1

tl;dr, your problem is your database manager. The solution is for him/her to undo the damage done by restoring the data to its previous state. Just delete the row. Updating the primary key never works.

Do not create a solution or validate his/her mistake by solving it, as doing so will make it more likely to happen again.

---

Full answer.

Your actual problem is your "database administrator" who is violating a cardinal rule of databases: Never update surrogate key values!

In your case the situation is even more tragic because the gaps in the ID column values ​​don't matter anyway. If the gap does matter, you're even worse off. Please allow me to explain...

The author name is your actualidentifier. We know this because it has a unique constraint.

The

ID column is a surrogate key, most conveniently implemented as an auto-incrementing integer, but surrogate keys will also work fine if the surrogate key is a random (unique) number. The gap, or even the choice of the value itself, has nothing to do with the validity of the surrogate key.