Import some database entries through PHPMyAdmin with overwrite

Question

I exported a couple of entries from a database I have stored locally on my MySQL dbase through PhpMyAdmin and I'd like to replace only those entries on my destination database hosted online. Unfortunately when I try to do so PHPMyAdmin says that those posts already exist and therefore he can't erase them.

It'll take me a lot of time to search for those entries manually within the rest of the posts and delete them one at a time so I was wondering if there's any workaround in order to overwite those entries on import.

Thanks in advance!

Solution 1

This is a quick and dirty way to do it. Others may have a better solution:

It sounds like you're trying to run INSERT queries, and phpMyAdmin is telling you they already exist. If you use UPDATE queries, you could update the info.

I would copy the queries you have there, into a text editor, preferably one that can handle find and replace, like Notepad++ or Gedit, and then replace some code to change the queries around from INSERT to UPDATE.

See: http://dev.mysql.com/doc/refman/5.0/en/update.html

OR, you could just delete them, then run your INSERT queries.

You might be able to use some logic with find and replace to make a DELETE query that gets rid of them first.

http://dev.mysql.com/doc/refman/5.0/en/delete.html

Solution 2

A great option is to handle this on your initial export from phpMyAdmin locally. When exporting from phpMyAdmin:

1. Export method: Custom
2. Format: SQL
3. Format-specific options - choose "data" (instead of "structure" or "structure and data")
4. In Data creation options - Function to use when dumping data: Switch "Insert" to "Update" <-- This is the ticket!
5. Click Go!

Import into your production database. (always backup your production database before hand just in case)

I know this is an old post, but it actually helped me find a solution built into phpMyAdmin. Hope it helps someone else!

Solution 3

Check out insert on duplicate. You can either add the syntax to your entries stored locally, or import into a temporary database, then run an INSERT ... SELECT ... ON DUPLICATE KEY UPDATE. If you could post a schema, it would help us guide you better.

Comments

[MACC]

Thanks, I had lost the reference of the exported posts because I mass deleted a column across all rows so I went back to a previous backup, deleted the entries whose column data I needed to keep and only then I erased the data from the rest, afterwards I imported the first ones with data back into place.

[Chud37]

This was really helpful, thanks, exactly what I was looking for :D