pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to update table to make dup values distinct
3+ messages / 3 participants
[nested] [flat]
* how to update table to make dup values distinct
@ 2005-11-10 15:58 george young <gry@ll.mit.edu>
0 siblings, 2 replies; 3+ messages in thread
From: george young @ 2005-11-10 15:58 UTC (permalink / raw)
To: pgsql-sql
[PostgreSQL 7.4RC2 on i686-pc-linux-gnu](I know, I know... must upgrade soon)
I have a table mytable like:
i | txt
---+-------
1 | the
2 | the
3 | rain
4 | in
5 | mainly
6 | spain
7 | stays
8 | mainly
9 | in
I want to update it, adding a ':' to txt so that each txt value is unique.
I don't care which entry gets changed. I tried:
update mytable set txt=mytable.txt || ':' from mytable t2 where mytable.txt=t2.txt and mytable.i=t2.i;
but this updated both duplicated entries.
Um, there may sometimes be 3 or 4 duplicates, not just two. For these, I can add multiple colons, or one each of an assortment of characters, say ':+*&^#'.
Performance does not matter here. The real table has 30K rows, ~200 dups.
To clarify, I want to end up with something like:
1 | the
2 | the:
3 | rain
4 | in
5 | mainly:
6 | spain
7 | stays
8 | mainly
9 | in:
-- George
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: how to update table to make dup values distinct
@ 2005-11-11 05:05 Bruno Wolff III <bruno@wolff.to>
parent: george young <gry@ll.mit.edu>
1 sibling, 0 replies; 3+ messages in thread
From: Bruno Wolff III @ 2005-11-11 05:05 UTC (permalink / raw)
To: george young <gry@ll.mit.edu>; +Cc: pgsql-sql
On Thu, Nov 10, 2005 at 10:58:18 -0500,
george young <gry@ll.mit.edu> wrote:
> [PostgreSQL 7.4RC2 on i686-pc-linux-gnu](I know, I know... must upgrade soon)
>
> I have a table mytable like:
> i | txt
> ---+-------
> 1 | the
> 2 | the
> 3 | rain
> 4 | in
> 5 | mainly
> 6 | spain
> 7 | stays
> 8 | mainly
> 9 | in
>
> I want to update it, adding a ':' to txt so that each txt value is unique.
> I don't care which entry gets changed. I tried:
This seems like an odd way to fix whatever problem you are having.
Assuming you really do want to go through with this, you can use oids to
distinguish rows. For example you could add a colon to the row with the
lowest oid for each repeated string, and keep doing that until you have them
all fixed.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: how to update table to make dup values distinct
@ 2005-11-11 14:38 Harald Fuchs <hf0923x@protecting.net>
parent: george young <gry@ll.mit.edu>
1 sibling, 0 replies; 3+ messages in thread
From: Harald Fuchs @ 2005-11-11 14:38 UTC (permalink / raw)
To: pgsql-sql
In article <20051110105818.4dc51e8c.gry@ll.mit.edu>,
george young <gry@ll.mit.edu> writes:
> [PostgreSQL 7.4RC2 on i686-pc-linux-gnu](I know, I know... must upgrade soon)
> I have a table mytable like:
> i | txt
> ---+-------
> 1 | the
> 2 | the
> 3 | rain
> 4 | in
> 5 | mainly
> 6 | spain
> 7 | stays
> 8 | mainly
> 9 | in
> I want to update it, adding a ':' to txt so that each txt value is unique.
> I don't care which entry gets changed. I tried:
> update mytable set txt=mytable.txt || ':' from mytable t2 where mytable.txt=t2.txt and mytable.i=t2.i;
> but this updated both duplicated entries.
> Um, there may sometimes be 3 or 4 duplicates, not just two. For these, I can add multiple colons, or one each of an assortment of characters, say ':+*&^#'.
> Performance does not matter here. The real table has 30K rows, ~200 dups.
> To clarify, I want to end up with something like:
> 1 | the
> 2 | the:
> 3 | rain
> 4 | in
> 5 | mainly:
> 6 | spain
> 7 | stays
> 8 | mainly
> 9 | in:
Try the following:
UPDATE mytable
SET txt = txt || substring ('::::::::::::::::' for (
SELECT count(*)
FROM mytable t1
WHERE t1.txt = mytable.txt AND t1.i < mytable.i
)::int)
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-11-11 14:38 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-10 15:58 how to update table to make dup values distinct george young <gry@ll.mit.edu>
2005-11-11 05:05 ` Bruno Wolff III <bruno@wolff.to>
2005-11-11 14:38 ` Harald Fuchs <hf0923x@protecting.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox