CODEWITHSUNDEEP
mysqldatabaseimportphpmyadminexport
MACC
MACC

Reputation: 335

Import some database entries through PHPMyAdmin with overwrite

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!

Upvotes: 8

Views: 17040

Answers (3)

Matt Mintun
Matt Mintun

Reputation: 626

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!

Upvotes: 11

Kermit
Kermit

Reputation: 34054

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.

Upvotes: 2

PanicGrip
PanicGrip

Reputation: 141

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

Upvotes: 5

Related Questions