so_56204, Sql Server

Language: Layout:

-- tabla [items]
-- Gem1, Gem2, Gem3, Gem4, Gem5, Gem6
-- 23    23    23    23    23    23
-- 31    23    34    2     34    5
-- 32    3     35    23    23    23

-- estructura de la tabla
create table items (
    id int identity(1,1),
    Gem1 int,
    Gem2 int,
    Gem3 int,
    Gem4 int,
    Gem5 int,
    Gem6 int)
go

-- versión inicial de los datos
insert into items values (23, 23, 23, 23, 23, 23)   -- 23 duplicado 6 veces
insert into items values (31, 23, 34, 2, 34, 5)     -- 34 duplicado 2 veces
insert into items values (32, 3, 35, 23, 23, 23)    -- 23 duplicado 3 veces
insert into items values (5, 23, 14, 8, 2, 1)       -- NINGÚN DUPLICADO
insert into items values (8, 8, 14, 23, 23, 5)      -- 8 duplicado dos veces; 23 duplicado dos veces

select * from items
go

-- generación de tabla vertical de datos
create table #items_vert (
    id int,
    nombre varchar(max),   -- NOTA: en realidad el nombre (Gem1, Gem2, Gem3) no es necesario para la operación; pero se pone como ilustración
    num int)
go

insert into #items_vert
select id, 'Gem1', (select Gem1 from items where id = i.id) num from items i
union
select id, 'Gem2', (select Gem2 from items where id = i.id) num from items i
union
select id, 'Gem3', (select Gem3 from items where id = i.id) num from items i
union
select id, 'Gem4', (select Gem4 from items where id = i.id) num from items i
union
select id, 'Gem5', (select Gem5 from items where id = i.id) num from items i
union
select id, 'Gem6', (select Gem6 from items where id = i.id) num from items i

select * from #items_vert
go

-- generación de tabla de duplicados
select id, num, count(*) cta into #dups from #items_vert
group by id, num having count(*) > 1

select * from #dups
go

-- actualización de duplicados en tabla original
update items set Gem1 = 0
where exists (select * from #dups where id = items.id and num = items.Gem1)

update items set Gem2 = 0
where exists (select * from #dups where id = items.id and num = items.Gem2)

update items set Gem3 = 0
where exists (select * from #dups where id = items.id and num = items.Gem3)

update items set Gem4 = 0
where exists (select * from #dups where id = items.id and num = items.Gem4)

update items set Gem5 = 0
where exists (select * from #dups where id = items.id and num = items.Gem5)

update items set Gem6 = 0
where exists (select * from #dups where id = items.id and num = items.Gem6)

select * from items
go

View schema
Execution time: 0,08 sec, rows selected: 45, rows affected: 50, absolute service time: 0,27 sec