MySQL 5.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table digitalassets (Id int,SongCode int, SoundRecordingCode int
  ,ISRC varchar(10),ContractId int);
insert  into digitalassets values
 (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)
;
select * from digitalassets;

Records: 10  Duplicates: 0  Warnings: 0




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


/* 
UPDATE digitalassets t1
    JOIN (SELECT Id
         , (select MAX(SoundRecordingCode)
            FROM digitalassets d2 where d2.ISRC=d1.ISRC) AS MaxSoundRecordingCode
         , ISRC
          FROM digitalassets d1
          WHERE ContractId = 43
          ) t2 USING(Id)
    SET t1.SoundRecordingCode = t2.MaxSoundRecordingCode;
*/
UPDATE digitalassets t1
    JOIN (SELECT Id, MAX(SoundRecordingCode)over(partition by ISRC) AS MaxSoundRecordingCode, ISRC
          FROM digitalassets
          WHERE ContractId = 43
          ) t2 USING(Id)
    SET t1.SoundRecordingCode = t2.MaxSoundRecordingCode;

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(partition by ISRC) AS MaxSoundRecordingCode, ISRC
          FROM digitalassets
' at line 2


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;

Rows matched: 10  Changed: 7  Warnings: 0


select * from digitalassets;


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

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