Delete

• sumit.malik

  SSC Journeyman

  Points: 87

  More actions

  June 11, 2003 at 10:22 pm

  #159991

  hi folks,

  my question is rather simple.

  i have one table say emp in which i have one coloum named "names" and in that i have few duplicate entries like...

  Rita

  Sita

  Gita

  Tita

  Rita

  Rita

  so in this i have three names as Rita

  so i just want to delete duplicate records only i.e. only two Rita's should be deleted leaving out the one, so that the table should look like

  Rita

  Sita

  Gita

  Tita

  thanks in advance

• Crispin Proctor

  SSCoach

  Points: 18161

  More actions

  June 11, 2003 at 10:43 pm

  #460949

  Got this from someone on this site. I cannot take credit for it.

  I cannot remember who it was...

  ALTER TABLE TableWithDuplicates ADD EliminateDuplicates_RowID int NOT NULL IDENTITY(1, 1)

  DELETE

  FROM t1

  FROMTableWithDuplicates t1

  JOIN(

  SELECTEliminateDuplicates_RowID=MAX(EliminateDuplicates_RowID),

  Field1,

  Field2,

  Field3,

  Field4

  FROMTableWithDuplicates

  GROUP BY Field1,

  Field2,

  Field3,

  Field4

  HAVINGCOUNT(*) >= 2

  ) t2

  ON(t1.EliminateDuplicates_RowID <> t2.EliminateDuplicates_RowID

  ANDt1.Field1 = t2.Field1

  ANDt1.Field2 = t2.Field2

  ANDt1.Field3 = t2.Field3

  ANDt1.Field4 = t2.Field4)

  ALTER TABLE TableWithDuplicates DROP COLUMN EliminateDuplicates_RowID

  Cheers,

  Crispin

  Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!