Skip site navigation (1) Skip section navigation (2)

Re: Removing duplicate entries

From: "Scott Ford" <Scott(dot)Ford(at)bullfrogpower(dot)com>
To: "Michael Fuhr" <mike(at)fuhr(dot)org>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Removing duplicate entries
Date: 2006-01-11 21:21:45
Message-ID: CAA10533F41BB7448F7659D4D0671907347CB7@cx41.800onemail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Okay, let me elaborate a little more and maybe it will help.  Although I
think this is along the right track.

Okay - here's the problem in more detail:

customer
	customer_id (pk)
	...

documentation
	documentation_id (pk)
	customer_id (fk)
	document_type_id (fk)
	accepted

document_types
	document_type_id (pk)
	document_name
	...

What I want to do is remove duplicate entries for the same
customer_id/document_type_id for only a certain document_type_id.

-----Original Message-----
From: Michael Fuhr [mailto:mike(at)fuhr(dot)org] 
Sent: January 11, 2006 3:18 PM
To: Scott Ford
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Removing duplicate entries

On Wed, Jan 11, 2006 at 02:06:53PM -0500, Scott Ford wrote:
> customers
> 	customer_id
> 	...	
> 
> documents
> 	customer_id
> 	document_id
> 	document_type_id
> 	...
> 
> So, for example, there are two documents with the same
document_type_id
> associated with one customer.
> 
> Can someone help me with a SQL statement that might help me remove the
> duplicate documents for a certain document_type_id?

Is document_id a primary key (or otherwise unique)?  If so then
something like this might work:

DELETE FROM documents WHERE document_id NOT IN (
  SELECT min(document_id)
  FROM documents
  GROUP BY customer_id, document_type_id
);

Be sure to understand what this query does before running it; I
might be making assumptions about your data that aren't correct.
I'd advise trying this or any other suggestion against test data
before using it on data you don't want to lose, and I'd also recommend
using a transaction that you can roll back if necessary (i.e., start
a transaction, run the delete, run some queries to make sure the
changes are correct, then either commit or roll back the transaction).

-- 
Michael Fuhr

Responses

pgsql-novice by date

Next:From: Michael GlaesemannDate: 2006-01-11 23:37:35
Subject: Re: Sequential Scans On Complex Query With UNION - see why this fails
Previous:From: Steve TucknottDate: 2006-01-11 21:03:27
Subject: Re: Sequential Scans On Complex Query With UNION - see

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group