Andreas you could either use the system columns oid or ctid.
The ctid will always be available, but the oid will only be available
if you created the table with "with oids" syntax( > version 8.0).
UPDATE status_table
SET status_id = -1
WHERE ctid = (SELECT MIN(RMV.ctid)
FROM status_table RMV
WHERE 1 = 1
AND RMV.ctid <> ctid
AND RMV.c_date = c_date
AND RMV.status_id = status_id
AND RMV.name = name
)
Mario
Andreas wrote:
Hi,
I'd like to update some records in a table.
Those have a status_id and among other columns a varchar with a name
and a create_date.
The status_id is 0 if nothing was done with this record, yet.
For some reasons I've got double entries which I now want to flag to
-1 so that they can be sorted out without actually deleting them since
there are other tables referencing them.
From every group that shares the same name all should get status_id
set to -1 where status_id = 0.
The tricky bit is:
How could I provide, that 1 of every group survives, even then when
all have status_id = 0?
Sometimes 2 of a group are touched so both have to stay.
e.g.
c_date, status_id, name
2008/01/01, 0, A --> -1
2008/01/02, 1, A --> do nothing
2008/01/03, 0, A --> -1
2008/01/01, 0, B --> do nothing (single entry)
2008/01/01, 0, C --> do nothing (oldest 0 survives)
2008/01/02, 0, C --> -1
2008/01/01, 1, D --> do nothing
2008/01/02, 1, D --> do nothing
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
