Skip to main content

Identify the duplicates in MySQL

To identify the duplicate entries in MySQL, use below query.

                     SELECT COUNT(*) as repetitions, group_concat(id, ' (', startTime, ', ', endTime, ') ' SEPARATOR ' | ') as row_data FROM GROUP BY startTime, endTime HAVING repetitions > 1

FYI,
  1. id, startTime and endTime columns should be changed based on your table schema.
  2. you can change the output format here like alias and the separator.
Labels:

Comments

Post a Comment

Popular posts from this blog

Backup and restore database in SQL Server

To backup the database, use below query. Backup database <databaseName> to disk ='<backupLocation>.bak' with init; To backup the logs, use below query. Backup log <databaseName> to disk =' <backupLocation> .trn' with init; To restore from the database backup, use below query. restore database <databaseName> from disk = <backupLocation>.bak ' with norecovery, replace, move 'testmirror' to 'DATA.mdf', move 'testmirror_log' to 'DATA_log.ldf'; To restore from the transaction log, use below query. restore log <databaseName> from disk =' <backupLocation> .trn ' with recovery, replace, move 'testmirror' to 'DATA.mdf', move 'testmirror_log' to 'DATA_log.ldf'
Post a Comment
Read more

Execute queries from SQL files

To execute queries from a SQL file, use below query. MySQL: There are 2 options: If you are in MySQL command line, execute below query. source 'SQLfilePath'; If you are in command line/shell, execute below query. mysql -u userName -p password database < 'SQLfilePath' MSSQL: Use T-SQL, execute below query. sqlcmd -S databaseName -i "SQLfilePath" -o "outputFilePath"
Post a Comment
Read more

Rename database name in MSSQL

To rename the database is MSSQL, use below queries. ALTER DATABASE srcDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO sp_rename ' srcDatabaseName' , ' dest DatabaseName ' ,'DATABASE'; GO ALTER DATABASE dest DatabaseName SET MULTI_USER;
Post a Comment
Read more