joseph Posted May 16, 2007 Share Posted May 16, 2007 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? ??? Quote Link to comment https://forums.phpfreaks.com/topic/51665-whats-the-difference-between-the-export-sql-dump-type-used-in-phpmyadmin/ Share on other sites More sharing options...
hitman6003 Posted May 17, 2007 Share Posted May 17, 2007 Export a small table with each method and look at the differences.... http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html Quote Link to comment https://forums.phpfreaks.com/topic/51665-whats-the-difference-between-the-export-sql-dump-type-used-in-phpmyadmin/#findComment-254982 Share on other sites More sharing options...
Barand Posted May 18, 2007 Share Posted May 18, 2007 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) Quote Link to comment https://forums.phpfreaks.com/topic/51665-whats-the-difference-between-the-export-sql-dump-type-used-in-phpmyadmin/#findComment-256205 Share on other sites More sharing options...
davidannis Posted February 12, 2013 Share Posted February 12, 2013 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? Quote Link to comment https://forums.phpfreaks.com/topic/51665-whats-the-difference-between-the-export-sql-dump-type-used-in-phpmyadmin/#findComment-1411985 Share on other sites More sharing options...
davidannis Posted February 12, 2013 Share Posted February 12, 2013 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? Quote Link to comment https://forums.phpfreaks.com/topic/51665-whats-the-difference-between-the-export-sql-dump-type-used-in-phpmyadmin/#findComment-1412026 Share on other sites More sharing options...
fenway Posted February 12, 2013 Share Posted February 12, 2013 There are tools that will do this -- mysqldump isn't one of them -- you're talking about diff-ing records and schemata. Quote Link to comment https://forums.phpfreaks.com/topic/51665-whats-the-difference-between-the-export-sql-dump-type-used-in-phpmyadmin/#findComment-1412063 Share on other sites More sharing options...
davidannis Posted March 4, 2013 Share Posted March 4, 2013 fenway, Can you suggest a good tool for me. It might save me a lot of time. Quote Link to comment https://forums.phpfreaks.com/topic/51665-whats-the-difference-between-the-export-sql-dump-type-used-in-phpmyadmin/#findComment-1416327 Share on other sites More sharing options...
teynon Posted March 4, 2013 Share Posted March 4, 2013 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. Quote Link to comment https://forums.phpfreaks.com/topic/51665-whats-the-difference-between-the-export-sql-dump-type-used-in-phpmyadmin/#findComment-1416329 Share on other sites More sharing options...
teynon Posted March 4, 2013 Share Posted March 4, 2013 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. Quote Link to comment https://forums.phpfreaks.com/topic/51665-whats-the-difference-between-the-export-sql-dump-type-used-in-phpmyadmin/#findComment-1416331 Share on other sites More sharing options...
fenway Posted March 9, 2013 Share Posted March 9, 2013 The Percona Toolkit has something called pt-table-sync. Quote Link to comment https://forums.phpfreaks.com/topic/51665-whats-the-difference-between-the-export-sql-dump-type-used-in-phpmyadmin/#findComment-1417739 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.