Optimize updating multiple rows with one query

Question

I have used this query on many occasions and never had an issue. I just like to know is this the best option when updating 10,000 plus rows with 1 query?

I am updating and making changes to a postal codes database and some of these can get quite large.

This is what I am using (imagine it being 10,000 plus postal codes)

UPDATE postalcodes
SET user = CASE postal
  WHEN 'M4C 1A1' THEN '1'
  WHEN 'M4C 1A2' THEN '1'
  WHEN 'M4C 1A3' THEN '1'
END
WHERE postal IN ('M4C 1A1','M4C 1A2','M4C 1A3')

In all cases when updating the USER will always be the same.

Am I doing this correctly or is there a more faster, optimized solution? Thanks.

Answer 1

As this is affecting like 1000 rows you can do it as transaction.

If in between some thing happened and some are updated and some are not updated you have to check again.

If you use transaction commit and roll back that will be safe,

You can try . but db engine should be innodb . you have to change it from Myisam

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

Answer 2

You can optimize away the case when the value is already set, so that you don't bother UPDATEing a row that doesn't need it.

UPDATE postalcodes
SET user = 1
WHERE postal IN ('M4C 1A1','M4C 1A2','M4C 1A3') and (user <> 1)