Monday, August 16, 2010

MySQL - Duplicates Handling with Insert Ignore / Replace

CREATE TABLE person_tbl
(
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10),
UNIQUE (last_name, first_name)
);

INSERT IGNORE INTO person_tbl VALUES( 'Thomas', 'Jay', 'Male'); - This query will try to insert and if the record already exist it ignores to insert. It checks unique / primary key

REPLACE INTO person_tbl (last_name, first_name, sex) VALUES( 'Jay', 'Thomas', 'FeMale'); - This query will try to replace if the same unique / primary key record already exist. If record not exist it will insert.

No comments :

// Below script tag for SyntaxHighLighter