Duplicate Order Remover

 
select bachnumb, docdate, (select count(*) from sop10200 as d where d.soptype=oh.soptype and d.sopnumbe=oh.sopnumbe),  
    (select count(*) from sop30300 as d where d.soptype=oh.soptype and d.sopnumbe=oh.sopnumbe),  
    * from sop10100 as oh where exists (select 1 from sop30200 as hh where hh.soptype=oh.soptype and hh.sopnumbe=oh.sopnumbe)  
  
select * from sop10100 as oh where exists (select 1 from sop30200 as hh where hh.soptype=oh.soptype and hh.sopnumbe=oh.sopnumbe)  
select * from sop10200 as oh where exists (select 1 from sop30200 as hh where hh.soptype=oh.soptype and hh.sopnumbe=oh.sopnumbe)  
  
  
begin tran  
  
delete oh from sop10200 as oh where exists (select 1 from sop30200 as hh where hh.soptype=oh.soptype and hh.sopnumbe=oh.sopnumbe)  
delete oh from sop10100 as oh where exists (select 1 from sop30200 as hh where hh.soptype=oh.soptype and hh.sopnumbe=oh.sopnumbe)  
  
rollback tran
--commit tran

Leave a Reply