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...@gmx.net>
To: "PostgresSQL list" <pgsql-sql@postgresql.org>
Cc: "Oliveiros Cristina" <oliveiros.crist...@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...@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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
