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

Tags:

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)

Comments

Post new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.

If you enjoyed this post, make sure you subscribe to our RSS Feed! Or if you prefer, you can Follow us on Twitter instead.

This is my notes page -- a scratch pad. Sometimes I do not write them correctly. I wrote here so I can access my notes from everywhere. If you want to use them, use with caution. Please check and re-check the syntax. You've been warned!