From: Harald Fuchs <hf0923x@protecting.net>
To: pgsql-sql@postgresql.org
Subject: Re: how to update table to make dup values distinct
Date: 11 Nov 2005 15:38:49 +0100
Message-ID: <871x1n5jwm.fsf@srv.protecting.net> (raw)
References: <20051110105818.4dc51e8c.gry@ll.mit.edu>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: hf0923x@protecting.net
Subject: Re: how to update table to make dup values distinct
In-Reply-To: <871x1n5jwm.fsf@srv.protecting.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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