declare @main_language nvarchar(50);
declare @child_language nvarchar(50);

set @main_language = 'EN'
set @child_language = 'SW'

delete from ts_list_entries
where list_entry_language = @child_language

select
list_entry_id as le_id,
english_language,
other_language,
list_entry_language
into #tmp_list_entry_lang_count
from
(
    select
    le_list_id,
    le_key collate SQL_Latin1_General_CP1_CS_AS as le_key,
    max(english_language) as english_language,
    max(other_language) as other_language

      from
      (
            select
               list_entry_list_id as le_list_id,
               list_entry_key as le_key, list_entry_language,
            case when list_entry_language = @main_language then 1 else 0 end as english_language,
            case when list_entry_language = @child_language then 1 else 0 end as other_language
            from ts_list_entries
      ) as list_entry_lang_count
      group by
      le_list_id,
      le_key collate SQL_Latin1_General_CP1_CS_AS

) as unique_list_entry_lang_count
inner join ts_list_entries on list_entry_list_id = le_list_id and list_entry_key = le_key
where not(other_language = 1 and english_language = 1)
and list_entry_language not in(
      select registrynode_name from ts_registry_node
      where registrynode_parent_node_id is NULL
      and registrynode_table = 'R'
      and registrynode_name != @main_language
)

-- delete non-english entries that don't have an correspoding english entry
delete from ts_list_entries
where list_entry_id in (select le_id from #tmp_list_entry_lang_count where other_language = 1 and english_language = 0)


-- Save Copy entries that only have an english entry but don't have a corresponding non-english entry
INSERT INTO [ts_list_entries]
           (
           [list_entry_record_state]
           ,[list_entry_audit_id]
           ,[list_entry_create_audit_id]
           ,[list_entry_list_id]
           ,[list_entry_id]
           ,[list_entry_language]
           ,[list_entry_key]
           ,[list_entry_label]
           ,[list_entry_unique_label]
           ,[list_entry_sort_hint]
           ,[list_entry_status]
           ,[list_entry_version]
           )
select
           0
           ,'{00000000-0000-0000-0000-000000000000}'
           ,'{00000000-0000-0000-0000-000000000000}'
           ,[list_entry_list_id]
           ,newid()
           ,@child_language
           ,[list_entry_key]
           ,[list_entry_label]
           ,[list_entry_unique_label]
           ,[list_entry_sort_hint]
           ,[list_entry_status]
           ,0
from ts_list_entries
inner join #tmp_list_entry_lang_count on le_id = list_entry_id
where other_language = 0 and english_language = 1
go

drop table #tmp_list_entry_lang_count
go