Deleting Duplicate Records using MySQL

I recently had a table which ended up with some duplicate records being imported. A simple way to achieve this is to use a subquery as follows:

If you want to keep the row with the lowest id value:

If you want the id value that is the highest:

Original Article: Stack Overflow

Clearing All Product Data

If you have your test site setup and its time to make room for your real product catalog you can clear out all the product data using the following queries:

Please note, this does not reset the auto increment values on the tables, and don’t forget to update your table prefix (if you have one).

Making a copy of your customer and order data for Magento 1.9.2

If you have setup a new Magento installation to match your live website and want to move accross the old orders and your customer data then follow the following steps.

I prefer to import my database tables via shell rather than using PHPMyAdmin as it has a tendency to hang if you have a lot of data

Exporting the Tables from your live database

To export the correct tables simply export any tables which begin with sales_ and customers_:

You will want to export them with the ‘Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT statement’ checked.

Once you have exported these files from PHPMyAdmin you will need to open up the files and add the following line to the beginning of the file:

And this line to the very end of the file:

This disables the foreign key checks for the duration of the import, it might not be recommended normally but as the target database is “empty” it fine to do.

Importing via Shell

Upload your sql file to your server and then navigate to it in your shell panel, once there run the following command (replacing the place holders with your settings):

you will then be prompted for the password, enter it and hit return and the file will be imported into the database. Just to ensure everything comes in smoothly I recommend clearing the cache and then log out of your target Magento store and log back in and you should find your dashboard is now up to date with your latest orders and customers.