MySQL - Update max value where duplicates occur in separate column

Question

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.

Answer 1

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.