Обсуждение: select first occurrence of a table
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?
Suggestions?
Thanks!
-X
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 toohard to write a function in PL/PGSQL to fill an added key column. 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? > > Suggestions? > > Thanks! > > -X >
=?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
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:
>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
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:
>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.