Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm running a 'bash for' script to update some records in mysql, like so:

for i in `cat yahoo.txt`; do mysql $DB --batch -fe "update users set email=concat(left(email, instr(email, '@')), 'yahoo.com') where email like '%@$i';" ; done

but even with --force, the update stops after the first error:

ERROR 1062 (23000) at line 1: Duplicate entry 'example@yahoo.com' for key 3

Is there anyway I can force it to continue?

share|improve this question
    
What leads you to believe it isn't continuing? That script should do fine, unless your shell has been interestingly configured. –  womble Jul 25 '12 at 9:36
    
I do a select for the mistyped emails (before and after executing the script) and the numbers are the same. –  w00t Jul 25 '12 at 10:00

1 Answer 1

up vote 1 down vote accepted

try changing update to update ignore

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

With the IGNORE keyword, the update statement does not abort even if errors occur during the update. Rows for which duplicate-key conflicts occur are not updated. Rows for which columns are updated to values that would cause data conversion errors are updated to the closest valid values instead.

share|improve this answer
    
thank you! didn't had a clue that option existed. –  w00t Jul 25 '12 at 9:58
    
there's also insert ignore - skips the inserts for the cases where item with identical value [for primary key, unique indices] already exists. –  pQd Jul 25 '12 at 12:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.