Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 18-05-2009
Member
 
Join Date: Apr 2009
Posts: 68
How to perform Insert on duplicate key update in MySQL

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.
Reply With Quote
  #2  
Old 18-05-2009
Member
 
Join Date: Dec 2008
Posts: 161
Re: How to perform Insert on duplicate key update in MySQL

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.
Reply With Quote
  #3  
Old 18-05-2009
Member
 
Join Date: Jan 2009
Posts: 140
Re: How to perform Insert on duplicate key update in MySQL

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.
Reply With Quote
  #4  
Old 18-05-2009
Member
 
Join Date: Mar 2008
Posts: 232
Re: How to perform Insert on duplicate key update in MySQL

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

Code:
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.

Code:
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)
);
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to perform Insert on duplicate key update in MySQL"
Thread Thread Starter Forum Replies Last Post
SqlException: Cannot insert duplicate key row in object 'dbo.tbDep Leo Pold Server Update Service 3 07-01-2014 09:51 AM
How to Insert data into MySQL table Mahendra varma Software Development 4 04-10-2009 04:42 AM
Insert Images in MySQL from FTP MeteoWatch Software Development 2 02-07-2009 01:06 AM
Batch insert on MySQL is possible Shreevats Software Development 3 16-05-2009 06:13 PM
how to insert a image in mysql cnu0870 Software Development 5 28-04-2009 05:15 PM


All times are GMT +5.5. The time now is 02:08 AM.