bradwall
Level: Smiter

Rank Points: 423
Registered: 02-12-2003
IP: Logged

I have a query that I am running... it looks like this:
select suppliercode, suppliername, count(suppliercode)
from pasupplier
group by suppliercode, suppliername;

I want to delete any duplicated entries in this table. I want it to keep one of the records and delete any left over. So, if the count is greater than 1, I want it to delete all of the records except for one of them. I can't find an easy way of doing this.

Any ideas?

EDIT: P.S. I am using PostgreSQL and it doesn't have Stored Procedures.

[Edited by bradwall at Local Time:02-11-2005 at 04:00 PM]

Schik
Level: Legendary Smitemaster

Rank Points: 5383
Registered: 02-04-2003
IP: Logged

Can't you do this? :

DELETE FROM pasupplier WHERE suppliercode = $savedCode AND suppliername = $savedName LIMIT $savedCount-1

Where the $variables are from your previous query?

____________________________
The greatness of a nation and its moral progress can be judged by the way it treats its animals.
--Mahatma Gandhi

bradwall
Level: Smiter

Rank Points: 423
Registered: 02-12-2003
IP: Logged

Schik wrote:
Can't you do this? :

DELETE FROM pasupplier WHERE suppliercode = $savedCode AND suppliername = $savedName LIMIT $savedCount-1

Where the $variables are from your previous query?

Tried that earlier... It gives me a parse error at LIMIT.
I found a way around this, and finished what I needed to do. It was a longer way of going it, but it is the way I need it now.

Thanks