Denis Arh wrote:
How to delete "real" duplicates?
id | somthing
---
1 | aaa
1 | aaa
2 | bbb
2 | bbb
(an accident with backup recovery...)
In these cases, its certainly the best to rebuild your table
using a
CREATE TABLE new AS
SELECT col1,col1..
FROM old
GROUPY BY col1,col2..
Hi,
Would this be OK or a little crude (untested) :
INSERT INTO new_table ( id, something )
SELECT
DISTINCT ON (id)
id,
something
FROM old_table
ORDER BY id
Or something similar but create a new table ?
Cheers
Rudi.
Denis Arh wrote:
How to delete "real" duplicates?
id | somthing
"Denis Arh" <[EMAIL PROTECTED]> writes:
> How to delete "real" duplicates?
Use the OID or CTID system columns.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if
> How to delete "real" duplicates?
>
> id | somthing
> ---
> 1 | aaa
> 1 | aaa
> 2 | bbb
> 2 | bbb
>
> (an accident with backup recovery...)
I'm not 100% on some of the syntax off the top of my head, but:
BEGIN;
ALTER TABLE orig_table RENAME TO backup_table;
CREATE TABLE ori
gt;
Cc: <[EMAIL PROTECTED]>
Sent: Sunday, June 22, 2003 11:17 PM
Subject: Re: [SQL] Delete duplicates
> try this
>
> DELETE FROM aap WHERE id NOT IN (
>SELECT max(id)
>FROM aap
>GROUP BY keyword
> );
>
> >
> >
> > Hi,
> >
>
try this
DELETE FROM aap WHERE id NOT IN (
SELECT max(id)
FROM aap
GROUP BY keyword
);
>
>
> Hi,
>
> I have a table with duplicates and trouble with my SQL.
> I'd like to keep a single record and remove older duplicates.
> For example below of the 6 recods I'd like to keep records
> 4 a
Hi,
you need find duplicates and then you remove them
delete from aap where id not in ( select max(id) from aap b where
aap.keyword = b.keyword );
Germán
Sorry about my english
-Mensaje original-
De: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] En nombre de Rudi Starcevic
Enviado el: Do
On 22/06/2003 10:15 Rudi Starcevic wrote:
Hi,
I have a table with duplicates and trouble with my SQL.
I'd like to keep a single record and remove older duplicates.
For example below of the 6 recods I'd like to keep records
4 and 6.
TABLE: aap
id | keyword
+-
1 | LEAGUE
On Sunday 22 June 2003 11:15, Rudi Starcevic wrote:
> Hi,
>
> I have a table with duplicates and trouble with my SQL.
(...)
> select a1.id
> from aap a1
> where id < ( SELECT max(id) FROM aap AS a2 )
> AND EXISTS
> (
> SELECT *
> FROM aap AS a2
> WHERE a1.keyword = a2.keyword
> )
How about (unte
