CODEWITHSUNDEEP
mysqlsql
Chris
Chris

Reputation: 4728

Updating multiple rows using IN where the same ID appears multiple times

I have a query where I update multiple rows:

update history set goals=goals+3 where playerID IN (1,2,3)

This works well but I could potentially have hundreds of player IDs. These playerIDs may be repeated, e.g.

update history set goals=goals+3 where playerID IN (1,2,3,4,5,1,2)

In this case I'd want playerIDs 1 and 2 to have their goals incremented by 6 whereas playerIDs 3, 4 and 5 should only be incremented by 3.

Based on my query each unique playerID is incremented just by 3

Is there any way to overcome this with MySQL without needing run multiple queries and manipulate the data before hand?

Upvotes: 1

Views: 58

Answers (3)

Rajat
Rajat

Reputation: 5793

Is there any way to overcome this with MySQL without needing run multiple queries and manipulate the data before hand?

I don't recommend this, so this is just to demonstrate the technical feasibility, which I understood to be part of your question

set session group_concat_max_len = 1200000;
set @players = (select group_concat(playerId) from players);

update goals 
set goal=goal+3*((char_length(@players)) - char_length(replace((@players),playerID,'')));

Demo

Upvotes: 1

Passiv Programmer
Passiv Programmer

Reputation: 341

While this is possible to implement in SQL, as pointed out by Gordon Linoff, it should not be done this way. To write understandable and maintainable code you should divide data manipulation and logical structures.

This would easily be achived by going through the array with a loop preparing the goals that should be inserted into the database.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270021

You need to preaggregate. YOu can do this as:

update history h join
       (select playerId, count(*) as cnt
        from (select 1 as playerId union all
              select 2 as playerId union all
              select 3 as playerId union all
              select 4 as playerId union all
              select 1 as playerId union all
              select 2 as playerId
             ) p
        group by playerid
       ) p
       using (playerid)
    set goals = goals + cnt * 3;

Fortunately or unfortunately, an update updates each row only once. So you need to precalculate the increment, either in the application or the query. Or run multiple updates.

Upvotes: 1

Related Questions