CODEWITHSUNDEEP
mysql
Vivek Buddhadev
Vivek Buddhadev

Reputation: 871

Update MySQL table and ignore duplicate entries

I created a table that contains a UNIQUE 'mobile_no' like

09727048248
9727048248
9824578564
9898998998

Then I am going to check whether or not the mobile number is valid, and if it's valid then I want to change it into the proper format like 919727048248.

For that I called update query like..

update bccontacts 
set mobile_no='919727048248' 
where mobile_no=09727048248

The first time it ran successfully, but the second time it replied

ERROR 1062 (23000):Duplicate entry '919727048248' for key 'mobile_no'

Because there is already a unique key set for the 'mobile_no'.

So is there any other query which will IGNORE DUPLICATE KEY ON UPDATE?

Upvotes: 57

Views: 114865

Answers (2)

R R
R R

Reputation: 2956

If you have declared the mobile number as the primary key in your table, then you can’t have the same mobile number twice in your table. Have a look at the MySQL UPDATE documentation.

Upvotes: 0

Zaar Hai
Zaar Hai

Reputation: 9889

Use UPDATE IGNORE:

update IGNORE bccontacts 
set mobile_no='919727048248' 
where mobile_no=09727048248

More info here: http://dev.mysql.com/doc/refman/5.0/en/update.html

Upvotes: 124

Related Questions