Re: Delete 1 Record of 2 Duplicate Records

How do I delete only 1 of the duplicate records?

column_name | column_id
---------------------+--------------
test1 | 5
test1 | 5

I've tried this:

tmp_test=# delete from test where column_id = 5 limit 1;
ERROR: parser: parse error at or near "limit"

I'm using version 7.2.1

Thank you.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If they are truly identical, then you must use the hidden 'oid' column to
differentiate the two. No need to peek at the oid, just do this:

DELETE FROM test WHERE oid = (SELECT oid FROM test WHERE column_id=5 LIMIT 1);

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

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

iD8DBQE+OT+AvJuQZxSWSsgRAgZOAKCrwW2O/bQpxo5LBBp4vDkS8YoZ9wCg2H7N
R9R4CTSXx/lRmjm5NvZkYXE=
=VI0G
-----END PGP SIGNATURE-----

On Thu, 30 Jan 2003 val(at)webtribe(dot)net wrote:

> How do I delete only 1 of the duplicate records?

Do

select oid,* from test where column_id = 5;

then choose which oid to delete
and do

delete from test where oid = ...;

>
>
> column_name | column_id
> ---------------------+--------------
> test1 | 5
> test1 | 5
>
>
> I've tried this:
>
> tmp_test=# delete from test where column_id = 5 limit 1;
> ERROR: parser: parse error at or near "limit"
>
> I'm using version 7.2.1
>
> Thank you.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-10-8981112
fax: +30-10-8981877
email: achill(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)gr

greg(at)turnstep(dot)com writes:
> If they are truly identical, then you must use the hidden 'oid' column to
> differentiate the two. No need to peek at the oid, just do this:

> DELETE FROM test WHERE oid = (SELECT oid FROM test WHERE column_id=5 LIMIT 1);

Also, if you're unfortunate enough to have this problem in a table with
no OID column, you can play the same trick with the ctid column, which
always exists (it's the physical location of the tuple). ctid is
dangerous to use for most purposes, because it's not stable over UPDATE
or VACUUM FULL, but it works perfectly well for this kind of problem.

regards, tom lane