Z5QQhQd7

1drop table if exists appFilesX;
2
3create table appFilesX like app_files;
4
5
6
7drop function if exists allUpdate;
8
9create function allUpdate(pathGroup varchar(255) charset utf8 collate utf8_unicode_ci, fileId INT) returns int
10
11  begin
12
13    insert into appFilesX select * from app_files
14
15    where file_id is null and path=pathGroup;
16
17
18
19    update app_files set file_id = fileId
20
21    where file_id is null and path=pathGroup;
22
23
24
25    return 0;
26
27  end;
28
29
30
31drop table if exists appFilesPath;
32
33create table appFilesPath as
34
35  SELECT path, min(file_id) as u_file_id
36
37  FROM app_files
38
39  GROUP BY path
40
41  HAVING count(path) > 1 and u_file_id is not null;
42
43
44
45select sum(x)
46
47from (select *, allUpdate(path, u_file_id) as x from appFilesPath)