create or replace procedure SUNO_PROD.PROD.DIM_CLIP_DELETE_PROC()
returns number
language sql
execute as caller
as
$$
declare
    sql string;
begin   
    sql := '
        MERGE INTO dim_clip AS target
        USING (
            select 
                a.clip_id as clip_id
            from dim_clip as a
            left join clip as b on a.clip_id = b.id
            where b.id is null
        ) AS source ON (source.clip_id = target.clip_id)
        WHEN MATCHED THEN UPDATE
            SET 
                target.is_deleted = true;
    ';
    execute immediate :sql;
    RETURN SQLROWCOUNT;
end;
$$;
