9 replies to this topic

[joseph]

In PhpMyAdmin we have these 3 export options to choose from: 1. Insert 2. Update and 3. Replace. Sound very easy to understand but how is it done exactly to the tables having an existing data?

For sure, I know the most common when we use INSERT Export type to an existing database, it will insert it all w/o errors that is if the tables being inserted to the new database does not have any identical table_names.

How about for the UPDATE and REPLACE? How will it react when we insert them to the new database if there are identical table_names?  ???

[hitman6003]

Export a small table with each method and look at the differences....

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

[Barand]

Basically

INSERT inserts new records
UPDATE updates existing records
REPLACE inserts if record is not there and updates if it is (based on primary key match)

[davidannis]

Am I correct that there is no option that will delete a record if it no longer exists in the copy that is being imported?

[davidannis]

Let me explain what I am after a little better. I have a live database which I exported to my development machine. While it was there I added a few new tables, a few fields in existing tables, and deleted a bunch of really old records that nobody will ever need. I'd love to be able to export from the development machine, import to my test server, have the new tables added (easy), have the tables with new fields updated, and have the junk records deleted. I think that I need to actually write SQL myself to do the last two things though. Am I wrong?

[fenway]

There are tools that will do this -- mysqldump isn't one of them -- you're talking about diff-ing records and schemata.

[davidannis]

fenway, Can you suggest a good tool for me. It might save me a lot of time.

[teynon]

Basically

INSERT inserts new records
UPDATE updates existing records
REPLACE inserts if record is not there and updates if it is (based on primary key match)

Just to clarify, REPLACE does not update the old record. REPLACE will delete the old record and insert a new one. Be careful using REPLACE INTO if you have other tables that are referencing it.

[teynon]

Am I correct that there is no option that will delete a record if it no longer exists in the copy that is being imported?

If you just want the imported records and only those imported records, PHPMyAdmin has the option of adding the "DROP TABLE" statement. This is essentially like restoring a backup. I would be very careful doing this on a production site though.

[fenway]

The Percona Toolkit has something called pt-table-sync.