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
