Thread: 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
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 >
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 >> > >
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 > > >
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 >
The trick is to do a "GROUP BY" on your identifier (name) and then use a HAVING clause to see if the count is more then 1. NOTE: You likely need a query that does subqueries that use group by considering you want to ignore SOME of the records (ie one per group if that group does not have a status 1 record) but not others (update all in the group if the group has a status 1 record). Hopefully that's enough of a hint, but if not when I get a moment I can spell it out in more detail. NOTE: I recommend running a SELECT first, rather then an UPDATE, so you can see what WOULD be updated and verify your query is going to do what you want before you clobber data. (or use a transaction, but if its a live database you don't want a transaction around locking users out) Terry Terry Fielder terry@greatgulfhomes.com Associate Director Software Development and Deployment Great Gulf Homes / Ashton Woods Homes Fax: (416) 441-9085 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 > > >