FAQ: Deleting all but one identical row

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
NotDashEscaped: You need GnuPG to verify this message

Another pass at the "deleting all but one identical row" item for
the FAQ. Made it less wordy and more to the point. Hopefully
the examples work too. :)

--
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200303061421

Index: FAQ.html
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/FAQ/FAQ.html,v
retrieving revision 1.171
diff -c -r1.171 FAQ.html
*** FAQ.html 2003/02/18 17:23:08 1.171
--- FAQ.html 2003/03/06 19:24:50
***************
*** 139,144 ****
--- 139,145 ----
temporary tables in PL/PgSQL functions?<BR>
<A href="#4.27">4.27</A>) What replication options are available?<BR>
<A href="#4.28">4.28</A>) What encryption options are available?<BR>
+ <A href="#4.29">4.29</A>) How can I delete all but one identical row?<BR>


<H2 align="center">Extending PostgreSQL</H2>
***************
*** 1380,1385 ****
--- 1381,1404 ----
<I>PASSWORD_ENCRYPTION</I> in <I>postgresql.conf</I>.</LI>
<LI>The server can run using an encrypted file system.</LI>
</UL>
+
+ <H4><A name="4.29">4.29</A>) How can I delete all but one identical row?<BR>
+ </H4>
+ <P>If you have rows that can not be differentiated by a WHERE clause, and
+ want to delete all but one of the rows, you can use the
+ system column <CODE><SMALL>oid</SMALL></CODE>:</P>
+ <PRE>
+ DELETE FROM mytable WHERE mycol = 'xxx' AND oid !=
+ (SELECT oid FROM mytable WHERE mycol = 'xxx' LIMIT 1);
+ </PRE>
+
+ <P>Some tables may not have <CODE><SMALL>oid</SMALL></CODE>s, in which
+ case you can use the system column <CODE><SMALL>ctid</SMALL></CODE>
+ with a slightly different syntax:</P>
+ <PRE>
+ DELETE FROM mytable WHERE mycol = 'xxx' AND NOT ctid =
+ (SELECT ctid FROM mytable WHERE mycol = 'xxx' LIMIT 1);
+ </PRE>

<HR>

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+Z5/MvJuQZxSWSsgRAh5KAJ4iV7XBMbBCPBYNMW+2961BIZDKHQCeK61/
+FGT8s4ZvP75LgLruSgLZbo=
=vNk/
-----END PGP SIGNATURE-----