how to update table to make dup values distinct

From: george young <gry(at)ll(dot)mit(dot)edu>
To: pgsql-sql(at)postgresql(dot)org
Subject: how to update table to make dup values distinct
Date: 2005-11-10 15:58:18
Message-ID: 20051110105818.4dc51e8c.gry@ll.mit.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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)

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message codeWarrior 2005-11-10 16:25:18 Re: RETURNS SETOF table; language 'sql'
Previous Message Judith Altamirano Figueroa 2005-11-10 15:16:13 Re: time