in this article i want to show you an sql script that with use it , duplicate fields not inserted and ignored .
insert ignore into table(filed1,field2) values ('value1','value2')
translated from نرم افزار حسابداری
Avoid creating repeated records in a table
You can use a UNIQUE index or a PRIMARY KEY on the table with appropriate fields to avoid creating duplicate records.
Next, we will examine an example. In the table below, there is no index or PRIMARY KEY, and therefore it is possible to create duplicate records for first_name and last_name.
CREATE TABLE person_tbl (
first_name CHAR(20),
last_name CHAR(20),
sex CHAR(10)
);
To avoid creating multiple records with the same first and last name in this table, a PRIMARY KEY can be added to its definition. Thus, it is necessary that indexed columns are non-null, because PRIMARY KEY does not allow creating null values.
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)
);
With a unique index on a table, if you insert a record into the table that is a duplicate of a record in the column or columns on which the index is defined, it will normally result in an error.
NSERT IGNORE command
In this case, the INSERT IGNORE command can be used instead of the INSERT command. If a record is not a duplicate of an existing record, then MySQL will insert it normally; But if it is a duplicate, then the IGNORE keyword tells MySQL to ignore it without issuing any errors.
In the example below, no error is generated and duplicate records are not inserted in the table at the same time.
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)
REPLCAE command
REPLCAE command can be used instead of INSERT command. If the record is new, it will be inserted as when using the INSERT command; But if it is duplicate, the new record replaces the previous record.
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)
INSERT IGNORE and REPLACE commands should be chosen depending on the behavior we want to have with duplicate records. The INSERT IGNORE command keeps the previous record and ignores the new record. The REPLCAE command inserts the new duplicate version and deletes the old version.
Another way to require uniqueness is to add a UNIQUE index to the table instead of a PRIMARY KE:
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
Top comments (0)