Re: Rename entries with an increment

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