Mysql Question posted in
The official MySQL documentation can be found here.

MySQL – Update max value where duplicates occur in separate column

I am currently migrating some media assets to a 3rd party application (3PA), our internal system has the assets logged in the sample data below, however the 3PA requires the SoundRecordingCode be related to the ISRC so I need to run a bulk update statement against 10000’s of entries similar to my sample dataset.

Sample dataset:

| Id | SongCode | SoundRecordingCode | ISRC       | ContractId   |
|----|----------|--------------------|------------|--------------|
| 1  | 100      | 201                | ISRC_001   | 43           |
| 2  | 100      | 202                | ISRC_001   | 43           |
| 3  | 100      | 203                | ISRC_001   | 43           |
| 4  | 100      | 204                | ISRC_001   | 43           |
| 5  | 100      | 205                | ISRC_001   | 43           |
| 6  | 101      | 206                | ISRC_002   | 43           |
| 7  | 101      | 207                | ISRC_002   | 43           |
| 8  | 102      | 208                | ISRC_003   | 43           |
| 9  | 102      | 209                | ISRC_003   | 43           |
| 10 | 102      | 210                | ISRC_003   | 43           |

I am trying to update the value of SoundRecordingCode to MAX(SoundRecordingCode) where the ISRC is duplicated, therefore i would like the final result to be:

| Id | SongCode | SoundRecordingCode | ISRC       | ContractId   |
|----|----------|--------------------|------------|--------------|
| 1  | 100      | 205                | ISRC_001   | 43           |
| 2  | 100      | 205                | ISRC_001   | 43           |
| 3  | 100      | 205                | ISRC_001   | 43           |
| 4  | 100      | 205                | ISRC_001   | 43           |
| 5  | 100      | 205                | ISRC_001   | 43           |
| 6  | 101      | 207                | ISRC_002   | 43           |
| 7  | 101      | 207                | ISRC_002   | 43           |
| 8  | 102      | 210                | ISRC_003   | 43           |
| 9  | 102      | 210                | ISRC_003   | 43           |
| 10 | 102      | 210                | ISRC_003   | 43           |

I am using MySQL

I have tried the following update statement:

    UPDATE digitalassets t1
    JOIN (SELECT Id, MAX(SoundRecordingCode) AS MaxSoundRecordingCode, ISRC
          FROM digitalassets
          WHERE ContractId = 43
          GROUP BY ISRC
          HAVING COUNT(ISRC) > 1) t2 USING(Id)
    SET t1.SoundRecordingCode = t2.MaxSoundRecordingCode;

However this has only resulted in a single row being updated:

| Id | SongCode | SoundRecordingCode | ISRC       | ContractId   |
|----|----------|--------------------|------------|--------------|
| 1  | 100      | 205                | ISRC_001   | 43           |
| 2  | 100      | 202                | ISRC_001   | 43           |
| 3  | 100      | 203                | ISRC_001   | 43           |
| 4  | 100      | 204                | ISRC_001   | 43           |
| 5  | 100      | 205                | ISRC_001   | 43           |
| 6  | 101      | 207                | ISRC_002   | 43           |
| 7  | 101      | 207                | ISRC_002   | 43           |
| 8  | 102      | 210                | ISRC_003   | 43           |
| 9  | 102      | 209                | ISRC_003   | 43           |
| 10 | 102      | 210                | ISRC_003   | 43           |

Many thanks in advance.

Tags:

2

Answers


  1. 0

    The joining column should be the same column you’re grouping on in the subquery.

    UPDATE digitalassets t1
JOIN (SELECT MAX(SoundRecordingCode) AS MaxSoundRecordingCode, ISRC
      FROM digitalassets
      WHERE ContractId = 43
      GROUP BY ISRC
      HAVING COUNT(ISRC) > 1) t2 USING(ISRC)
SET t1.SoundRecordingCode = t2.MaxSoundRecordingCode;

    If you had the SQL mode only_full_group_by enabled, which has been the default since MySQL 5.7, you would have gotten an error from your query.

    Login or Signup to reply.
  2. 0

    It is necessary to calculate the maximum value SoundRecordingCode by ISRC.
    This calculation differs for MySql 8.0 and 5.5-5.7.

    See example

    UPDATE digitalassets t1
 JOIN (SELECT Id
        ,ISRC
        ,MAX(SoundRecordingCode)over(partition by ISRC) AS MaxSoundRecordingCode
      -- for MySql 5.5-5.7 use
      --,(select MAX(SoundRecordingCode) FROM digitalassets d2 
      --  where d2.ISRC=d1.ISRC) AS MaxSoundRecordingCode
       FROM digitalassets
       WHERE ContractId = 43
       ) t2 USING(Id)
    SET t1.SoundRecordingCode = t2.MaxSoundRecordingCode;

    Result

    Id SongCode SoundRecordingCode ISRC ContractId
    1 100 205 ISRC_001 43
    2 100 205 ISRC_001 43
    3 100 205 ISRC_001 43
    4 100 205 ISRC_001 43
    5 100 205 ISRC_001 43
    6 101 207 ISRC_002 43
    7 101 207 ISRC_002 43
    8 102 210 ISRC_003 43
    9 102 210 ISRC_003 43
    10 102 210 ISRC_003 43

    example

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search