SQLite 3.39

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

create table scenes(id integer primary key
  , created_at date, updated_at date, scene_id varchar(20));
✓
insert into scenes values
 (1,'2024-02-13','2024-03-05','vt-1105')     
,(2,'2024-02-14','2024-03-06','KA-338')      
,(3,'2024-02-15','2024-03-07','JU-182')      
,(4,'2024-02-16','2024-03-08','PX-118')      
,(5,'2024-02-17','2024-03-09','po-6339085')  
,(6,'2024-02-18','2024-03-10','la-426')
;
✓
select * from scenes;


id
created_at
updated_at
scene_id


1
2024-02-13
2024-03-05
vt-1105

2
2024-02-14
2024-03-06
KA-338

3
2024-02-15
2024-03-07
JU-182

4
2024-02-16
2024-03-08
PX-118

5
2024-02-17
2024-03-09
po-6339085

6
2024-02-18
2024-03-10
la-426


create table files(id int primary key, created_at date, updated_at date, filename varchar(20),scene_id  int);
✓
insert into files values
 (1,'2024-03-03','2024-03-06','KA-338-A_180_LR.mp4',   2)         
,(2,'2024-03-03','2024-03-06','KA-338-B_180_LR.mp4',   2)         
,(3,'2024-03-03','2024-03-06','KA-338-C_180_LR.mp4',   2)         
,(4,'2024-03-04','2024-03-07','JU-182-A_180_LR.mp4',   3)         
,(5,'2024-03-04','2024-03-07','JU-182-B_180_LR.mp4',   3)         
,(6,'2024-03-04','2024-03-07','JU-182-C_180_LR.mp4',   3)         
,(7,'2024-03-04','2024-03-07','JU-182-D_180_LR.mp4',   3)         
,(8,'2024-03-05','2024-03-07','PX-118-A_180_LR.mp4',   4)         
,(9,'2024-03-05','2024-03-07','PX-118-B_180_LR.mp4',   4)
;
✓
select * from files;


id
created_at
updated_at
filename
scene_id


1
2024-03-03
2024-03-06
KA-338-A_180_LR.mp4
2

2
2024-03-03
2024-03-06
KA-338-B_180_LR.mp4
2

3
2024-03-03
2024-03-06
KA-338-C_180_LR.mp4
2

4
2024-03-04
2024-03-07
JU-182-A_180_LR.mp4
3

5
2024-03-04
2024-03-07
JU-182-B_180_LR.mp4
3

6
2024-03-04
2024-03-07
JU-182-C_180_LR.mp4
3

7
2024-03-04
2024-03-07
JU-182-D_180_LR.mp4
3

8
2024-03-05
2024-03-07
PX-118-A_180_LR.mp4
4

9
2024-03-05
2024-03-07
PX-118-B_180_LR.mp4
4


insert into scenes (created_at,updated_at,scene_id)
select created_at,updated_at,scene_code 
from(
select *,s.scene_id as scene_code
  ,count(*)over(partition by s.scene_id) cnt
from files f
left join scenes s on s.id=f.scene_id
) x
where cnt>1
✓
select * from scenes;


id
created_at
updated_at
scene_id


1
2024-02-13
2024-03-05
vt-1105

2
2024-02-14
2024-03-06
KA-338

3
2024-02-15
2024-03-07
JU-182

4
2024-02-16
2024-03-08
PX-118

5
2024-02-17
2024-03-09
po-6339085

6
2024-02-18
2024-03-10
la-426

7
2024-03-04
2024-03-07
JU-182

8
2024-03-04
2024-03-07
JU-182

9
2024-03-04
2024-03-07
JU-182

10
2024-03-04
2024-03-07
JU-182

11
2024-03-03
2024-03-06
KA-338

12
2024-03-03
2024-03-06
KA-338

13
2024-03-03
2024-03-06
KA-338

14
2024-03-05
2024-03-07
PX-118

15
2024-03-05
2024-03-07
PX-118


 update files
   set  scene_id=new_scene_id
from  (
 select file_id, created_at,updated_at,scene_code ,scene_id,rn,old_scene_id,new_scene_id,srn,frn
 from(
select *,s.scene_id as scene_code,f.scene_id old_scene_id,s.id new_scene_id,f.id file_id
  ,row_number()over(partition by s.scene_id order by f.id) rn
from (select f0.*,s0.scene_id main_scene 
        ,row_number()over(partition by f0.scene_id order by f0.id) frn
      from files f0 left join scenes s0 on f0.scene_id=s0.id
  ) f
left join (select * 
      ,row_number()over(partition by s1.scene_id order by s1.id) srn
    from scenes s1
  ) s on s.scene_id=f.main_scene  and (s.srn-1)=f.frn
 ) x
)y
where y.file_id=files.id
✓
select * from files;


id
created_at
updated_at
filename
scene_id


1
2024-03-03
2024-03-06
KA-338-A_180_LR.mp4
11

2
2024-03-03
2024-03-06
KA-338-B_180_LR.mp4
12

3
2024-03-03
2024-03-06
KA-338-C_180_LR.mp4
13

4
2024-03-04
2024-03-07
JU-182-A_180_LR.mp4
7

5
2024-03-04
2024-03-07
JU-182-B_180_LR.mp4
8

6
2024-03-04
2024-03-07
JU-182-C_180_LR.mp4
9

7
2024-03-04
2024-03-07
JU-182-D_180_LR.mp4
10

8
2024-03-05
2024-03-07
PX-118-A_180_LR.mp4
14

9
2024-03-05
2024-03-07
PX-118-B_180_LR.mp4
15


select * from scenes


id
created_at
updated_at
scene_id


1
2024-02-13
2024-03-05
vt-1105

2
2024-02-14
2024-03-06
KA-338

3
2024-02-15
2024-03-07
JU-182

4
2024-02-16
2024-03-08
PX-118

5
2024-02-17
2024-03-09
po-6339085

6
2024-02-18
2024-03-10
la-426

7
2024-03-04
2024-03-07
JU-182

8
2024-03-04
2024-03-07
JU-182

9
2024-03-04
2024-03-07
JU-182

10
2024-03-04
2024-03-07
JU-182

11
2024-03-03
2024-03-06
KA-338

12
2024-03-03
2024-03-06
KA-338

13
2024-03-03
2024-03-06
KA-338

14
2024-03-05
2024-03-07
PX-118

15
2024-03-05
2024-03-07
PX-118

id	created_at	updated_at	scene_id
1	2024-02-13	2024-03-05	vt-1105
2	2024-02-14	2024-03-06	KA-338
3	2024-02-15	2024-03-07	JU-182
4	2024-02-16	2024-03-08	PX-118
5	2024-02-17	2024-03-09	po-6339085
6	2024-02-18	2024-03-10	la-426

id	created_at	updated_at	filename	scene_id
1	2024-03-03	2024-03-06	KA-338-A_180_LR.mp4	2
2	2024-03-03	2024-03-06	KA-338-B_180_LR.mp4	2
3	2024-03-03	2024-03-06	KA-338-C_180_LR.mp4	2
4	2024-03-04	2024-03-07	JU-182-A_180_LR.mp4	3
5	2024-03-04	2024-03-07	JU-182-B_180_LR.mp4	3
6	2024-03-04	2024-03-07	JU-182-C_180_LR.mp4	3
7	2024-03-04	2024-03-07	JU-182-D_180_LR.mp4	3
8	2024-03-05	2024-03-07	PX-118-A_180_LR.mp4	4
9	2024-03-05	2024-03-07	PX-118-B_180_LR.mp4	4