Move rows between tables, avoid duplicate and update auto increment value.

Table “books” and “books_tmp” is identical.

“books” table is production state.
“books_tmp” is sandbox/testing/pre-production state.

Data comes in csv files >> dumped to “books_tmp” >> checked, sorted, etc. >> good data moved to “books”, row by row.

Before moved, “books_tmp” data is checked for duplicates in “books”, using ISBN (unique).
Upon inserted into “books”, (auto increment) id is checked for duplicate, if so, update the id.

This is the query I use (MySQL):
INSERT INTO books
SELECT * FROM books_tmp
WHERE books_tmp.ISBN NOT IN (SELECT ISBN FROM books)
ON DUPLICATE KEY UPDATE books.id = LAST_INSERT_ID(books.id)

Tagged with 
About sepedatua
I am nothing special, of this I am sure. I am a common man with common thoughts and I’ve led a common life. There are no monuments dedicated to me and my name will soon be forgotten, but I’ve loved another with all my heart and soul, and to me, this has always been enough.

Leave a Reply