Thread: select first occurrence of a table

select first occurrence of a table

From
"Johnson, Shaunn"
Date:

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

Re: select first occurrence of a table

From
"scott.marlowe"
Date:
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.


Re: select first occurrence of a table

From
Erik Ronström
Date:
> 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


Re: select first occurrence of a table

From
Erik Ronström
Date:
> 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


Re: select first occurrence of a table

From
nolan@celery.tssi.com
Date:
> > 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


Re: select first occurrence of a table

From
Dennis Gearon
Date:
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
>


Re: select first occurrence of a table

From
Tom Lane
Date:
=?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


Status of OIDs was: Re: select first occurrence of a table

From
Benjamin Scherrey
Date:
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


Re: Status of OIDs was: Re: select first occurrence of a table

From
Alvaro Herrera
Date:
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)


Re: Status of OIDs was: Re: select first occurrence of a table

From
Benjamin Scherrey
Date:
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.