Re: [SQL] Delete duplicates

2003-06-25 Thread Dani Oderbolz
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..

Re: [SQL] Delete duplicates

2003-06-22 Thread Rudi Starcevic
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

Re: [SQL] Delete duplicates

2003-06-22 Thread Tom Lane
"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

Re: [SQL] Delete duplicates

2003-06-22 Thread Sean Chittenden
> 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

Re: [SQL] Delete duplicates

2003-06-22 Thread Denis Arh
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, > > >

Re: [SQL] Delete duplicates

2003-06-22 Thread Franco Bruno Borghesi
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

Re: [SQL] Delete duplicates

2003-06-22 Thread Germán Gutiérrez
: Domingo, 22 de Junio de 2003 5:15 Para: [EMAIL PROTECTED] Asunto: [SQL] Delete duplicates 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. TABL

Re: [SQL] Delete duplicates

2003-06-22 Thread Paul Thomas
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

Re: [SQL] Delete duplicates

2003-06-22 Thread Ian Barwick
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

[SQL] Delete duplicates

2003-06-22 Thread Rudi Starcevic
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 PANTHERS 2 | LEAGUE PANTHERS 3 | LEA