Tables or result sets at times contain copy records. Some of the time, it is permitted yet now and again it is needed to stop copy records. Some of the time, it is needed to distinguish copy records and eliminate them from the table. This part will portray how to forestall copy records happening in a table and how to eliminate previously existing copy records.
Preventing Duplicates from Occurring in a Table
You can utilize a PRIMARY KEY or UNIQUE Index on a table with fitting fields to stop copy records. How about we take one model: The accompanying table contains no such file or essential key, so it would permit copy records for first_name and last_name.
CREATE TABLE person_tbl ( first_name CHAR(20), last_name CHAR(20), sex CHAR(10) );
To forestall various records with a similar first and last name esteems from being made in this table, add a PRIMARY KEY to its definition. At the point when you do this present, it's likewise important to announce the ordered segments to be NOT NULL, on the grounds that a PRIMARY KEY doesn't permit NULL qualities −
CREATE TABLE person_tbl ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), PRIMARY KEY (last_name, first_name) );
The presence of an extraordinary file in a table typically makes a mistake happen on the off chance that you embed a record into the table that copies a current record in the section or segments that characterize the list.
Use INSERT IGNORE as opposed to INSERT. In the event that a record doesn't copy a current record, MySQLi embeds it not surprisingly. On the off chance that the record is a copy, the IGNORE catchphrase advises MySQLi to dispose of it quietly without creating a blunder.
Following model doesn't blunder out and same time it won't embed copy records.
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name) -> VALUES( 'Jay', 'Thomas'); Query OK, 1 row affected (0.00 sec) mysql> INSERT IGNORE INTO person_tbl (last_name, first_name) -> VALUES( 'Jay', 'Thomas'); Query OK, 0 rows affected (0.00 sec)
Use REPLACE instead of INSERT. In the event that the record is new, it's embedded similarly likewise with INSERT. In the event that it's a copy, the new record replaces the former one −
mysql> REPLACE INTO person_tbl (last_name, first_name) -> VALUES( 'Ajay', 'Kumar'); Query OK, 1 row affected (0.00 sec) mysql> REPLACE INTO person_tbl (last_name, first_name) -> VALUES( 'Ajay', 'Kumar'); Query OK, 2 rows affected (0.00 sec)
Supplement IGNORE and REPLACE ought to be picked by the copy dealing with conduct you need to impact. Supplement IGNORE keeps the first of a bunch of copied records and disposes of the rest. Supplant keeps the remainder of a bunch of copies and delete out any previous ones.
Another approach to uphold uniqueness is to add a UNIQUE file instead of a PRIMARY KEY to a table.
CREATE TABLE person_tbl ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10) UNIQUE (last_name, first_name) );
Counting and Identifying Duplicates
Following is the inquiry to check copy records with first_name and last_name in a table.
mysql> SELECT COUNT(*) as repetitions, last_name, first_name -> FROM person_tbl -> GROUP BY last_name, first_name -> HAVING repetitions > 1;
This inquiry will restore a rundown of the relative multitude of copy records in person_tbl table. When all is said in done, to recognize sets of qualities that are copied, do the accompanying −
- Figure out which sections contain the qualities that might be copied.
- Rundown those segments in the section choice rundown, alongside COUNT(*).
- Rundown the segments in the GROUP BY condition also.
- Add a HAVING condition that disposes of one of a kind qualities by requiring bunch tallies to be more prominent than one.
Eliminating Duplicates from a Query Result:
You can utilize DISTINCT alongside SELECT articulation to discover one of a kind records accessible in a table.
mysql> SELECT DISTINCT last_name, first_name -> FROM person_tbl -> ORDER BY last_name;
An option in contrast to DISTINCT is to add a GROUP BY condition that names the segments you're choosing. This has the impact of eliminating copies and choosing just the interesting mixes of qualities in the predefined segments −
mysql> SELECT last_name, first_name -> FROM person_tbl -> GROUP BY (last_name, first_name);
Removing Duplicates Using Table Replacement
In the event that you have copy records in a table and you need to eliminate all the copy records from that table, at that point here is the methodology −
mysql> CREATE TABLE tmp SELECT last_name, first_name, sex -> FROM person_tbl; -> GROUP BY (last_name, first_name); mysql> DROP TABLE person_tbl; mysql> ALTER TABLE tmp RENAME TO person_tbl;
A simple method of eliminating copy records from a table is to add an INDEX or PRIMAY KEY to that table. Regardless of whether this table is as of now accessible, you can utilize this procedure to eliminate copy records and you will be protected in future also.
mysql> ALTER IGNORE TABLE person_tbl -> ADD PRIMARY KEY (last_name, first_name);