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 sor
Andreas,
Does your table has any field that can be used as primary key? Any "ID"
field?
Best,
Oliveiros
- Original Message -
From: "Andreas"
To:
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.
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 Mess
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_
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 pla
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 stat
