Possible Duplicate:
Truncate all tables in a MySQL database in one command?
I need to delete, or drop, all the tables in a MySQL database with single command without knowing the tablenames. Is this possible?
It can be done by many ways in sql server the most simplest way to do so is: Insert the distinct rows from the duplicate rows table to new temporary table. Then delete all the data from duplicate rows table then insert all data from temporary table which has no duplicates as shown below.
SQL Delete Duplicate Rows using Group By and Having Clause According to Delete Duplicate Rows in SQL, for finding duplicate rows, you need to use the SQL GROUP BY clause. The COUNT function can be used to verify the occurrence of a row using the Group by clause, which groups data according to the given columns.
Preventing Duplicates from Occurring in a Table. You can use a PRIMARY KEY or a UNIQUE Index on a table with the appropriate fields to stop duplicate records. Let us take an example – The following table contains no such index or primary key, so it would allow duplicate records for first_name and last_name.
drop database YOUR_DATABASE; /* this will delete all the tables for this database */ create database YOUR_DATABASE; /* added back the database namespace */
Rather long, but try this command (after replacing the obvious things):
mysql --user=YOUR_USERNAME --password=YOUR_PASSWORD -BNe "show tables" YOUR_DBSCHEMA_NAME | tr '\n' ',' | sed -e 's/,$//' | awk '{print "SET FOREIGN_KEY_CHECKS = 0;DROP TABLE IF EXISTS " $1 ";SET FOREIGN_KEY_CHECKS = 1;"}' | mysql --user=YOUR_USERNAME --password=YOUR_PASSWORD YOUR_DBSCHEMA_NAME
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With