Deleting duplicate records 2

[christer99]

How do I delete duplicate records? I am having problem with this

delete from policyhistory
where recidx in (select count(RECIDX), RECIDX from policyhistory
group by recidx
having count(recidx) > 1)

I have column called POLHID which is the actual ID and they are all unique. I have multiple rows with the same recidx which I am trying to get rid of

 

[mrdenny]

You can only have one column in the sub query of an in statement. Without having your schema I think this will do the trick. Test it on a dev server first.

delete from policyhistory
where POLHID in 
   (select min(POLHID) POLHID from 
      (select a.RECIDX, b.POLHID from 
         (select RECIDX, count(*) ct from policyhistory group by RECIDX having count(*) <> 1) c
      ) a
      join policyhistory b on a.RECIDX = b.RECIDX
   )

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[r937]

mrdenny, does that actually run?

you are joining [a] to , but [a] has a column from defined within it

i think you should be joining [c] to

r937.com | rudy.ca

 

[mrdenny]

I just created the table and your right I did screw it up.

delete from policyhistory
where POLHID in 
   (select min(POLHID) POLHID from 
      (select c.RECIDX, b.POLHID from 
         (select RECIDX, count(*) ct from policyhistory group by RECIDX having count(*) <> 1) c
         join policyhistory b on c.RECIDX = b.RECIDX
      ) a
   )

I put a couple of dummy rows in and it appears to work correctly.

This returns the rows correctly.

create table policyhistory
(POLHID int, RECIDX int, info varchar(10))

insert into policyhistory
select 1, 1, 'test1'

insert into policyhistory
select 2, 1, 'test2'

delete from policyhistory
where POLHID in 
   (select min(POLHID) POLHID from 
      (select c.RECIDX, b.POLHID from 
         (select RECIDX, count(*) ct from policyhistory group by RECIDX having count(*) <> 1) c
         join policyhistory b on c.RECIDX = b.RECIDX
      ) a
   )

select * from policyhistory

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[amrita418]

If your criteria is the keep the row with min polhid for duplicate records on recidx, try this:

Delete  a 
from    PolicyHistory a,
        (select     recidx, 
                    min(polhid) min_polhid
         from       PolicyHistory 
         group by   recidx) b
where   a.recidx = b.recidx 
        and a.polhid != b.min_polhid

PS: Code Not Tested.

Regards,
AA

 

[vongrunt]

Humph... my coffee level in blood is still low... but what about simply:

delete 
from policyhistory
where POLHID not in
(	select min(POLHID)
	from policyhistory
	group by RECIDX
)

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)