Thread: Rename entries with an increment

Hi, here is my issue. I have a table containing names. If a name appears
several times, I would like to rename it to add an incremented number to it.
For example, if 'Peter' appears three times in the table, I would like to
rename the first 'Peter' entry to 'Peter-1', the second one to 'Peter-2' and
so on. However, I began to do something but this doesn't seem to work at all
^^. Could someone please help me ? Thanks :)


DECLARE num int;

UPDATE test.suscribers
SET p_name = CONCAT(p_name,'-',num)
WHERE p_name=
(
    SELECT name
    FROM
    (
        SELECT *
        FROM
            (
                SELECT test.suscribers.p_name AS nom, COUNT(*) AS nb
                FROM test.suscribers
                GROUP BY test.suscribers.p_name
            )
        AS table1
        WHERE nb>1 AND wagaa != ''
    )
    AS table2
)



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Rename-entries-with-an-increment-tp5821237.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

On Wed, Oct 1, 2014 at 5:46 AM, JimmyJ <rdinh@hotmail.fr> wrote:
> Hi, here is my issue. I have a table containing names. If a name appears
> several times, I would like to rename it to add an incremented number to it.
> For example, if 'Peter' appears three times in the table, I would like to
> rename the first 'Peter' entry to 'Peter-1', the second one to 'Peter-2' and
> so on. However, I began to do something but this doesn't seem to work at all
> ^^. Could someone please help me ? Thanks :)
>
>
> DECLARE num int;
>
> UPDATE test.suscribers
> SET p_name = CONCAT(p_name,'-',num)
> WHERE p_name=
> (
>         SELECT name
>         FROM
>         (
>                 SELECT *
>                 FROM
>                         (
>                                 SELECT test.suscribers.p_name AS nom, COUNT(*) AS nb
>                                 FROM test.suscribers
>                                 GROUP BY test.suscribers.p_name
>                         )
>                 AS table1
>                 WHERE nb>1 AND wagaa != ''
>         )
>         AS table2
> )

This is pretty easy with a window function as long as you have a
unique identifier for ordering and updating.

UPDATE test.suscribers.p_name SET p_name =
concat(test.suscribers.p_name || '-' ||  q.num)
FROM
(
   SELECT p_name, row_number() OVER(PARTITION BY p_name ORDER BY id)
AS num -- try this inner query first, replace 'id' with whatever
field(s) are unique
)
WHERE test.suscribers.id = q.id;

merlin