Re: I need some magical advice - Mailing list pgsql-sql

Andreas,

This seems to work at least on the example you provided, but I
am not sure if this is what you want.
Also, I'm affraid this gets too slow if your table is very extense, due to 
the number of JOINS

It is possible that there is a more direct way to solve your problem,
but at least in plain SQL I couldn't find none :-(

Best,
Oliveiros

UPDATE t_your_table
SET status_id = -1
WHERE id
IN(
SELECT id
FROM t_your_table a
JOIN
(
SELECT c.name,MAX(c.status_id) as estado,MIN(d.oldest) as oldest
FROM t_your_table c
JOIN(
SELECT name,MIN(c_date) as oldest
FROM t_your_table
WHERE (status_id = 0)
GROUP BY name
HAVING (COUNT(*) > 1)) d
ON d.name = c.name
GROUP BY (c.name)
) b
ON a.name = b.name
AND (a.status_id <> b.estado
OR b.oldest <> a.c_date)
WHERE a.status_id = 0
)
----- Original Message ----- 
From: "Andreas" <maps.on@gmx.net>
To: "PostgresSQL list" <pgsql-sql@postgresql.org>
Cc: "Oliveiros Cristina" <oliveiros.cristina@marktest.pt>
Sent: Thursday, January 29, 2009 2:17 PM
Subject: Re: [SQL] I need some magical advice


> Hi,
>
> yes, there is a serial as primary key. Lets call it "id".
> Therfore one could use this to find the oldest record.
>
>
> Regards
>   Andreas
>
>
>
> Oliveiros Cristina schrieb:
>> Andreas,
>> Does your table has any field that can be used as primary key? Any "ID" 
>> field?
>>
>> Best,
>> Oliveiros
>>
>>
>> ----- Original Message ----- From: "Andreas" <maps.on@gmx.net>
>> To: <pgsql-sql@postgresql.org>
>> Sent: Thursday, January 29, 2009 11:56 AM
>> Subject: [SQL] I need some magical advice
>>
>>
>>> 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
>>>
>>
>>
>
>
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>