0

There two ways for performing MySql multi-update as pointed out at Multiple Updates in MySQL (first two Answers)

But, which is the better way (performance wise) of doing it if there are only updates and not inserts.

INSERT ... ON DUPLICATE KEY UPDATE

OR using CASE WHEN

Community
  • 1
  • 1
Kaushik
  • 3,371
  • 3
  • 23
  • 32

1 Answers1

1

One should measure.

If you choose between the two options, the first one is trying to insert a row and on unique constraint violation updates the data. The second approach requires a list of boolean checks for each field you want to update. The length of the list is the number of rows you want to update. That is for many rows is a bad choice.

The option with INSERT may be dangerous in case a parralel process deletes several of the rows you are trying to update. In this case it will try to do its primary task: insert the rows.

But there is another option: to join a series of rows separated by UNION ALL with a table you want to update. This is the option I usually tend to use.

UPD: I added an answer with UNION ALL to the linked question.

newtover
  • 31,286
  • 11
  • 84
  • 89
  • Good Idea, but, when we join a temp table it doesn't use index, do you think that could be a problem here? – Kaushik Sep 27 '13 at 17:20
  • The order of an inner join is chosen by the MySQL's optimizer. When you have a subquery which is joined to a real table (especially on the primary key), the join will always start from the subquery, that is the query is rather efficient. – newtover Sep 27 '13 at 18:42
  • @kaushik, just look at the EXPLAIN of the corresponding SELECT query. – newtover Sep 27 '13 at 18:50