select first occurrence of a table

On Fri, 2 May 2003, Johnson, Shaunn wrote:

Have a general SQL question:

I have a table that happens to have a some
duplicate entries in it (entire rows). I would like
to update one row and change one thing
about it, but it seems that I need a unique value
to do it; otherwise, I wind up changing multiple
rows.

For example: I have a table that looks like this

[snip table]

Table "survey_processed_dt"
Column | Type | Modifiers
-------------------+---------------------------+-----------
survid | character(8) |
processed_id | character varying(2) |
processed_dt | date |

[/snip table]

And the rows look like this:

[snip rows]

bcn=> select * from survey_processed_dt where survid = '02021000' and
processed_id = '05';
survid | processed_id | processed_dt
----------+--------------+--------------
02021000 | 05 | 2003-01-16
02021000 | 05 | 2003-01-16
(2 rows)

[/snip rows]

I want to change ONE of the rows ... is there a way to
just select the first occurrence of a row to update?

If you've got a table that was created with oids, you can do:

select *,oid from survey_processed_dt where survid = '02021000' and
processed_id = '05';

then you can change just the one with the OID.

I have a table that happens to have a some
duplicate entries in it (entire rows). I would like
to update one row and change one thing
about it, but it seems that I need a unique value
to do it; otherwise, I wind up changing multiple
rows.

If the table has OIDs (which is the default), you can use the OID as a
unique identifier for a row. But then you'll have to perform two
queries:

SELECT oid FROM table WHERE ... LIMIT 1;

UPDATE table SET ... WHERE oid = ...;

Erik

__________________________________________________
Yahoo! Plus
For a better Internet experience
http://www.yahoo.co.uk/btoffer

I have a table that happens to have a some
duplicate entries in it (entire rows). I would like
to update one row and change one thing
about it, but it seems that I need a unique value
to do it; otherwise, I wind up changing multiple
rows.

If the table has OIDs (which is the default), you can use the OID as a
unique identifier for a row. But then you'll have to perform two
queries:

SELECT oid FROM table WHERE ... LIMIT 1;

UPDATE table SET ... WHERE oid = ...;

Erik

__________________________________________________
Yahoo! Plus
For a better Internet experience
http://www.yahoo.co.uk/btoffer

I have a table that happens to have a some
duplicate entries in it (entire rows). I would like
to update one row and change one thing
about it, but it seems that I need a unique value
to do it; otherwise, I wind up changing multiple
rows.

If you don't care which of the two rows you change (and if they're
identical why would you?), you could do it with the following:

update foo set field1 = 'ZZZ' where oid =
(select min(oid) from foo where field2 = 'XXX');

max(oid) would work too.
--
Mike Nolan

That's why they have something called 'surrogate keys', usually a sequence column. google for that term. I wouldn't be too hard to write a function in PL/PGSQL to fill an added key column.

Johnson, Shaunn wrote:

Speaking of OIDs... I noticed that the talk is that these are being deprecated which, from my non
relationally purist/pro-object perspective, kinda disappointed me although I can guess at some
possible reasons. Is there some docs or a thread that someone can point me to that covers this
issue as I expect its been hashed over in depth already. I'd appreciate any information about the
justification and expected impact of this direction that Postgres is taking.

many thanx,

Ben Scherrey

5/2/2003 1:33:08 PM, Erik Ronstr�m <kvarken@yahoo.com> wrote:

=?iso-8859-1?q?Erik=20Ronstr=F6m?= <kvarken@yahoo.com> writes:

If the table has OIDs (which is the default), you can use the OID as a
unique identifier for a row.

You could also use CTID in the same way; this works on all tables.
It does require that no one else is trying to update the same row at
about the same time, though.

regards, tom lane

On Fri, May 02, 2003 at 04:26:27PM -0400, Benjamin Scherrey wrote:

Speaking of OIDs... I noticed that the talk is that these are being
deprecated which, from my non relationally purist/pro-object
perspective, kinda disappointed me although I can guess at some
possible reasons. Is there some docs or a thread that someone can
point me to that covers this issue as I expect its been hashed over in
depth already. I'd appreciate any information about the justification
and expected impact of this direction that Postgres is taking.

What do you mean by deprecated? They are not certainly going to
disappear. But user tables can be created without them, and it's
desirable to do so for a number of reasons.

If you want to have a unique identifier that's a single column and your
table has a multicolumn primary key, use another column tied to a
sequence. I don't know what other use you can give to an OID column in
a user table, but in this case you have the same overhead (4 bytes, or 8
if you need more space) with less problems, particularly wraparound.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La virtud es el justo medio entre dos defectos" (Aristoteles)

Perhaps I came away with the wrong impression but I thought that built-in oids were going away. If
they are still there by default but it is optional to build tables without them then I think that is great.
My request was really just for a pointer to info about this - not necessarily to rehash reasons or
options.

thanx & later,

Ben Scherrey

5/4/2003 7:06:11 PM, Alvaro Herrera <alvherre@dcc.uchile.cl> wrote: