How to avoid duplicates while updating a MySQL database?

Question

I'm receiving a MySQL dump file .sql daily from an external server, which I don't have any control of. I created a local database to store all data in the .sql file. I hope I can set up a script to automatically update my local database daily. The sql file I'm receiving daily contains old data that is in the local database already. How can I avoid duplicates of such old data and only insert into the local MySQL server new data? Thank you very much!

possible duplicate of [How to 'insert if not exists' in MySQL?](http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) — Danny Beckett, Jul 01 '13 at 14:52

score 2 · Answer 1 · answered Jul 01 '13 at 14:52

2

You can use a third-party database compare tool such as those from Red Gate to create two databases, one current (your "master") and the new dump. You can then run the compare tool between the two versions and update only changes between them, updating your master.

answered Jul 01 '13 at 14:52

Dave Swersky

34,502
9
78
118

+1 nice alternative but i think it's an "overkill" for the current context – Stephan Jul 01 '13 at 14:55
@Stephan Thanks. The OP didn't make any comments about how big the DB is, which/how many tables he wants to update. All other solutions posted assume a primary key in each table, the compare tool doesn't need one. – Dave Swersky Jul 01 '13 at 15:07

David Jashi · Answer 2 · 2013-07-01T14:57:20.683

1

Use unique constraints on field, that you want to be unique. Also, as Danny Beckett mentioned, to avoid errors in output (which I would prefer to redirect into file for future analysis, to check, if I haven't missed anything in process), you can use INSERT IGNORE construct instead of INSERT.

edited Jul 01 '13 at 14:57

answered Jul 01 '13 at 14:51

David Jashi

4,490
1
21
26

@Stephan I deleted my answer since Ganesh's is better, but thanks `:)` – Danny Beckett Jul 01 '13 at 14:53
1

@Stephan: Each answer should probably give an entire solution, or at least mention the other answer that has the rest of the solution... – Travesty3 Jul 01 '13 at 14:54
@DannyBeckett why would it fail to insert, by the way? Duplicate records will generate errors, the rest of "INSERT" statements will work nicely. – David Jashi Jul 01 '13 at 14:59
1

@DavidJashi You have a good point there, sorry about that! I removed my downvote. – Danny Beckett Jul 01 '13 at 15:01

score 0 · Answer 3 · answered Jul 01 '13 at 14:55

You can use a constraint supported with IGNORE statement.

The second option, you can first insert the data to a temp table. Then insert only the difference.

Using the second option you may use some restriction to do not search for duplication through add records stored in database.

score 0 · Answer 4 · answered Jul 01 '13 at 15:07

0

You need to create a primary key in your table. It should be a unique combination of column values. Using the INSERT query with IGNORE will avoid adding duplicates in this table.

see http://dev.mysql.com/doc/refman/5.5/en/insert.html

answered Jul 01 '13 at 15:07

ahPo

374
3
9

score 0 · Answer 5 · answered Jul 01 '13 at 15:13

If this is a plain vanilla mysqldump file, then normally it includes DROP TABLE IF EXISTS... statements and create table statements, so the tables are recreated when the data is imported. So duplicte data should not be a problem, unless I'm missing something.

How to avoid duplicates while updating a MySQL database?

5 Answers5

Related