Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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?
                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 9, 2018 at 15:50
                I would recommend against using UPDATE IGNORE as it is unsafe. As the documentation says: link "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 4, 2020 at 13:36
                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, 2013 at 8:37
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.