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
>
>
>
