update duplicate record

Question

I have a table with the following fields

Id Name IsPublic

i need to write a sql query that updates IsPublic to false where name has a duplicate. Only one of the duplicates should have IsPublic = true.

IsPublic is true by default

Answer 1

update
  table
set
  isPublic = false
from
  table t
    inner join table t2 on (t.name = t2.name and t.id < t2.id)
where
  isPublic = true

Answer 2

Should be setting isPublic to false for all the duplicates retaining the item with the mininmun ID for each group of items with the same value for the Name field:

UPDATE
    MyTable
SET 
    isPublic = false
WHERE   
    Id NOT IN
    (SELECT   
          MIN(Id)
     FROM 
          MyTable
     GROUP BY 
          Name 
    )

Answer 3

declare @t table(Id int, Name varchar, IsPublic bit)
insert into @t(Id, Name) values(1, 'a')
insert into @t(Id, Name) values(2, 'b')
insert into @t(Id, Name) values(3, 'b')

update a
set IsPublic = isnull( (select 0 from @t b where a.Name = b.Name and a.id > b.id), 1)
from @t a

select * from @t

Non-unique record with min id gets IsPublic = 1

Answer 4

Hello scrippie,

I am suggesting a sub-select approach:

update tableName t1
   set IsPublic = false
 where exists(select ID 
                from tableName t2 
               where t1.name = t2.name 
                 and t2.Id < t1.Id)

In order to ensure that exactly one of the duplicates keeps its IsPublic = true, I use an extra where clause in the sub-select: "and t2.Id < t1.Id". The duplicate with the lowest Id value keeps IsPublic = true, while all other records with the same name have their IsPublic set to false.

Cheers

Tom