Removing Duplicates Using Table Replacement This has the effect of removing duplicates and selecting only the unique combinations of values in the specified columns. Mysql> SELECT DISTINCT last_name, first_nameĪn alternative to the DISTINCT command is to add a GROUP BY clause that names the columns you are selecting. You can use the DISTINCT command along with the SELECT statement to find out unique records available in a table. List the columns in the GROUP BY clause as well.Īdd a HAVING clause that eliminates the unique values by requiring the group counts to be greater than one.Įliminating Duplicates from a Query Result List those columns in the column selection list, along with the COUNT(*). In general, to identify sets of values that are duplicated, follow the steps given below.ĭetermine which columns contain the values that may be duplicated. This query will return a list of all the duplicate records in the person_tbl table. Mysql> SELECT COUNT(*) as repetitions, last_name, first_name The REPLACE command keeps the last set of duplicates and erases out any earlier ones.Īnother way to enforce uniqueness is to add a UNIQUE index rather than a PRIMARY KEY to a table.įollowing is the query to count duplicate records with first_name and last_name in a table. The INSERT IGNORE command keeps the first set of the duplicated records and discards the remaining. The INSERT IGNORE and REPLACE commands should be chosen as per the duplicate-handling behavior you want to effect. Mysql> REPLACE INTO person_tbl (last_name, first_name) If it is a duplicate, the new record replaces the old one. If the record is new, it is inserted just as with INSERT. Use the REPLACE command rather than the INSERT command. Mysql> INSERT IGNORE INTO person_tbl (last_name, first_name) The following example does not error out and at the same time it will not insert duplicate records as well. If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error. If a record doesn't duplicate an existing record, then MySQL inserts it as usual. Use the INSERT IGNORE command rather than the INSERT command. The presence of a unique index in a table normally causes an error to occur if you insert a record into the table that duplicates an existing record in the column or columns that define the index. When you do this, it is also necessary to declare the indexed columns to be NOT NULL, because a PRIMARY KEY does not allow NULL values − To prevent multiple records with the same first and last name values from being created in this table, add a PRIMARY KEY to its definition. 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. You can use a PRIMARY KEY or a UNIQUE Index on a table with the appropriate fields to stop duplicate records. Preventing Duplicates from Occurring in a Table This chapter will describe how to prevent the occurrence of duplicate records in a table and how to remove the already existing duplicate records. It is required to identify duplicate records and remove them from the table. Most of the times it is allowed but sometimes it is required to stop duplicate records. Generally, tables or result sets sometimes contain duplicate records.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |