delete all but one record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with the following records (example)
Rec Num CardNum
1 123
2 123
3 123
4 999
5 999
6 888
What i wnt to do is keep ONLY one instance and delete the rest. e.g, delete
ANY two records of Card 123 and delete any record of card 999 and just leave
card 888 because the record count is ONE.
Can someone help in wrinting in code or querry? Thanks
 
1 123
2 123
3 123
4 999
5 999
6 888
What i wnt to do is keep ONLY one instance and delete the rest. e.g,
delete ANY two records of Card 123 and delete any record of card 999
and just leave card 888 because the record count is ONE.
Click to expand...

DELETE FROM MyTable AS Outer
WHERE Outer.RecNum IN
( SELECT Middle.RecNum FROM MyTable AS Middle
WHERE EXISTS
( SELECT * FROM MyTable AS Inner
WHERE Inner.CardNum = Middle.CardNum
AND Inner.RecNum > Middle.RecNum
)
)

I have not tested this, so treat it with care! In particular, test the
first SELECT to make sure it is identifying the correct records. The idea
is that the innermost select finds records with the same cardnum but a
higher recnum, leaving behind the lowest valued and unique ones; the outer
select returns the recnum (I am assuming it's the PK) and the delete will
erase them. Back up before running it!

HTH


Tim F
 
Thank you for the tip. but it (may) did not work. It has to go thru
too many records and takes a long time .
Click to expand...

I said I hadn't tested it: complex SQL off the top of my head rarely runs
first time. Did the SELECT part work?

As for time, it's probably possible to optimise it a bit by using JOINs
rather than subselects. The gurus live on m.p.a.adpsqlserver so you could
try asking them.

As for time (2) in database world you often end up with a choice between
getting it fast and getting it right. I am always surprised by how many
people choose the first one. If you really need both, then you just need a
faster processor. "There just ain't no substitute for them mips!"
I could simplify this by taking the RecNum filed out completely,
leaving just the CardNum field. I tried it but got confused. Can you
Help?
Click to expand...

Don't understand. If the RecNum is the primary key, then presumably it will
be referenced all over the database, so messing it up will involve a major
redesign.

If all you want is a list of distinct cardnum values, then just SELECT
DISTINCT Cardnum FROM Cards will do it. The original question said you
wanted to delete records.

Hope that helps


Tim F
 
Back
Top