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

Delete a record that exist in 6 tables at the same time

Hi! Is there a way to delete a record from multiple tables at the same time? Thanks for the help!

hmm not totally sure what you mean

Are you referring to atomically? So like nothing happens in between deleting them? if that's the case you have to do it as a transaction

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx

Is how you'd do it in code|||

Hi James25,

Is there a way to delete a record from multiple tables at the same time?

I would suggest you using triggers. You can create a trigger on one table and within that trigger defination, delete the corresponding records from other 5 tables.

For detailed information, please refer to this msdn document:http://msdn2.microsoft.com/en-us/library/ms189799.aspx

Hope my suggestion helps

|||

Thanks! That's what I am gonna implement.

Cheers!

|||

Thank you for the reply! Sorry, it took me a while to get back, I am gonna use a trigger for this problem.

|||

k

but be really careful about using triggers too extensively

they typically violate the seperation of layers

since they cause logic to happen unbeknownest to your business logic layers

|||

I'll keep that in mind. Thank you!