sql procedure

sql

drop table if exists appFilesX;

create table appFilesX like app_files;



drop function if exists allUpdate;

create function allUpdate(pathGroup varchar(255) charset utf8 collate utf8_unicode_ci, fileId INT) returns int

  begin

    insert into appFilesX select * from app_files

    where file_id is null and path=pathGroup;



    update app_files set file_id = fileId

    where file_id is null and path=pathGroup;



    return 0;

  end;



drop table if exists appFilesPath;

create table appFilesPath as

  SELECT path, min(file_id) as u_file_id

  FROM app_files

  GROUP BY path

  HAVING count(path) > 1 and u_file_id is not null;



select sum(x)

from (select *, allUpdate(path, u_file_id) as x from appFilesPath)