Re: delete duplicate orders

From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 04/12/04 

Date: Mon, 12 Apr 2004 22:54:47 +0530

  hi ashley,

  See following example:

  --sample data.

  create table #cartype(manufacturer varchar(500), itemnumber int)
  insert into #cartype values('Toyota',1)
  insert into #cartype values('Toyota',1)
  insert into #cartype values('Toyota',1)
  insert into #cartype values('Honda',2)
  insert into #cartype values('Honda',2)
  insert into #cartype values('Honda',3)
  insert into #cartype values('GE',3)

  --deleting all duplicate rows from the table, query will be.

  delete a
  from #cartype a join
  (select manufacturer, itemnumber
   from #cartype
   group by manufacturer, itemnumber
   having count(*) > 1) b on a.manufacturer = b.manufacturer and
a.itemnumber = b.itemnumber

  --if you want to keep one row out of the duplicate rows, you will have to
add an identity column to the table.

  Ex:

  alter table #cartype add idd int identity

  --deleting duplicate rows from the table except one

  delete from #cartype
  where not exists
  (Select * from #cartype a
   where a.manufacturer = #cartype.manufacturer
   and a.itemnumber = #cartype.itemnumber
   having min(idd) = #cartype.idd)

  --drop the temporary added column

  alter table #cartype drop column idd

  --
  Vishal Parkar
  vgparkar@yahoo.co.in