pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Delete 1 Record of 2 Duplicate Records 4+ messages / 4 participants [nested] [flat]
* Delete 1 Record of 2 Duplicate Records @ 2003-01-30 11:52 val@webtribe.net 0 siblings, 2 replies; 4+ messages in thread From: val@webtribe.net @ 2003-01-30 11:52 UTC (permalink / raw) To: pgsql-sql 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. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Delete 1 Record of 2 Duplicate Records @ 2003-01-30 15:04 greg@turnstep.com parent: val@webtribe.net 1 sibling, 1 reply; 4+ messages in thread From: greg@turnstep.com @ 2003-01-30 15:04 UTC (permalink / raw) To: pgsql-sql -----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@turnstep.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----- ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Delete 1 Record of 2 Duplicate Records @ 2003-01-30 16:00 Achilleus Mantzios <achill@matrix.gatewaynet.com> parent: val@webtribe.net 1 sibling, 0 replies; 4+ messages in thread From: Achilleus Mantzios @ 2003-01-30 16:00 UTC (permalink / raw) To: val@webtribe.net; +Cc: pgsql-sql On Thu, 30 Jan 2003 val@webtribe.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@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Delete 1 Record of 2 Duplicate Records @ 2003-01-31 01:16 Tom Lane <tgl@sss.pgh.pa.us> parent: greg@turnstep.com 0 siblings, 0 replies; 4+ messages in thread From: Tom Lane @ 2003-01-31 01:16 UTC (permalink / raw) To: greg@turnstep.com; +Cc: pgsql-sql greg@turnstep.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 ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-01-31 01:16 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2003-01-30 11:52 Delete 1 Record of 2 Duplicate Records val@webtribe.net 2003-01-30 15:04 ` greg@turnstep.com 2003-01-31 01:16 ` Tom Lane <tgl@sss.pgh.pa.us> 2003-01-30 16:00 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox