delete duplicate orders

I have an order with multiple line items. When the order comes in and put into a table I would like to check that they haven't submitted it twice. Right now I have a stored procedure that can find duplicate lines, but I really need if the whole order ha
s been duplicated then delete it.
So in my store procedure I use:
SELECT COUNT(*) AS Amount,ItemNumber,Store,DeliveryDate,submission,Qu antity
FROM tblItemOrder
GROUP BY ItemNumber,Store,DeliveryDate,submission,Quantity
HAVING COUNT(*) > 1
So this finds duplicate rows. But I need to find if the all rows are duplicated go ahead and delete.
The table structure is fields Amount,ItemNumber,Store,DeliveryDate,submission,Qu antity with no primary key (working table to get the data into shape).
Even if I could find if there are the same amount of rows that have duplicates COUNT(*) > 1, then go ahead and delete the duplicates that would work fine.
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