55

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?

TRiG
  • 10,148
  • 7
  • 57
  • 107
Vivek Buddhadev
  • 843
  • 1
  • 8
  • 9
  • Possible duplicate [4596390](http://stackoverflow.com/questions/4596390/mysql-insert-on-duplicate-key-do-nothing-just-dont-complain-to-the-error) – dbf Oct 22 '13 at 11:34

2 Answers2

120

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

Zaar Hai
  • 9,152
  • 8
  • 37
  • 45
  • 1
    I would also add a status column in order to be able to prune duplicates. UPDATE IGNORE targetTable SET status=9, targetCol=value ; DELETE FROM targetTable WHERE status !=9; – user3127882 Sep 09 '18 at 15:50
  • I would recommend against using UPDATE IGNORE as it is unsafe. As the documentation says: [link](https://dev.mysql.com/doc/refman/8.0/en/update.html) "UPDATE IGNORE statements, including those having an ORDER BY clause, are flagged as unsafe for statement-based replication. (**This is because the order in which the rows are updated determines which rows are ignored.***) Such statements produce a warning in the error log when using statement-based mode and are written to the binary log using the row-based format when using MIXED mode." – m144 Mar 04 '20 at 13:36
0

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.

TRiG
  • 10,148
  • 7
  • 57
  • 107
R R
  • 2,999
  • 2
  • 24
  • 42
  • 1
    Mobile number declared as UNIQUE_KEY, that i already mentioned in my question..by the way i got another solution see next answer.. – Vivek Buddhadev Nov 12 '13 at 08:37