How to perform Insert on duplicate key update in MySQL

[Xena]

I have number of records which are duplicate in nature and i found that it is very difficult to keep track of duplicate records manually because number user post duplicate records most of the time,when i had consult with the other people they have suggest me an insert on update key to achieve this, If anyone has any idea about how to do that please let me know.

[Alejandro]

INSERT ... ON DUPLICATE KEY UPDATE is very powerful but often forgotten MySQL feature. INSERT ON DUPLICATE KEY UPDATE is of the different sort - there is not much to learn about it - I think both features are really great and I use them both. They are implemented really in MySQL style of being simple powerful and easy to use.you can use it safely in your code. If someone does not know about it it takes to read single page to learn.

[RichiRich]

You might look at REPLACE, which is essentially a delete/insert if the record exists. But this would change the auto increment field if present, which could break relationships with other data. INSERT INTO TABLENAME(col1, col2) VALUES (’xxx’, ‘yyy’) ON DUPLICATE KEY UPDATE col1 = ‘zzz’
INSERT … ON DUPLICATE KEY UPDATE is appropriate when you must insert a record if it doesn’t exist, but just update some of its columns if the new record is a duplicate in the indexed columns.

[Shell]

The best way to use a PRIMARY KEY or UNIQUE Index on a table with appropriate fields to stop duplicate records. I will provide you the example, that will contains no such index or primary key, so it would allow duplicate records for first_name and last_name

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

To prevent multiple records with the same first and last name values from being created in this table,we will add a PRIMARY KEY to its definition. This is necessary to declare the indexed columns to be NOT NULL, because a PRIMARY KEY does not allow NULL values, you may see this as I will change the above example to its primary Key.

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)
);