CODEWITHSUNDEEP
mysqlsql
Michael
Michael

Reputation: 644

Update all names in a column incremental

I have a column "Customer". I would like to update all the names of the rows as such "Name1", "Name2", ... , "NameX".

If I do

UPDATE Customers
SET ContactName='Name1';

It sets every row to 'Name1'. How can I do this incremental? +1 for every name.

Upvotes: 2

Views: 180

Answers (4)

Jaumzera
Jaumzera

Reputation: 2379

Here's my solution in PostgreSQL:

In our case, we had some sales simulations with repeated names and our client asked us to prevent same user_id with repeated simulation name.

We had something like the following (sample simulation table):

 id     uid     name
  1       1     simu
  2       1     simu
  3       2     test
  4       2     simu
  5       2     test
  6       2     simu

which had to be turned into this:

 id     uid     name
  1       1     simu - 1
  2       1     simu - 2
  3       2     test - 1
  4       2     simu - 1
  5       2     test - 2
  6       2     simu - 2


-- Create a table to store duplicated names plus an index
-- row_number() over (partition by s1.name) will create a name with a number
-- for each duplicated name
create table sim_tmp_name as
select *
from (select s1.id, s1.name || ' - ' || row_number() over (partition by s1.name) as name
      from simulation s1
      where s1.id in (
          select t1.id
          from (
                   select *,
                          row_number() over (order by s.name)
                   from simulation s
                   where s.name in (
                       select t.name
                       from (select app_user_id, name, count(1)
                             from simulation
                             group by 1, 2
                             having count(1) > 1
                             order by 3 desc) as t)
                   order by s.name) as t1
      )) as sn;


-- Here we update our real table with the brand-new generated names in sim_tmp_name
update simulation s1
set name = (select s2.name from sim_tmp_name s2 where s2.id = s1.id)
where s1.id in (select s2.id from sim_tmp_name s2);

-- Here we create an index to avoid duplications
create unique index simulation_id_name_idx on simulation (app_user_id, upper(name));

-- Here we drop the temporary table
drop table sim_tmp_name;

Upvotes: 0

Janis S.
Janis S.

Reputation: 2626

set @i = 0;
update Customer 
set ContactName=concat('Name', @i := @i+1)

SQL Fiddle

Upvotes: 2

Chandan Rajput
Chandan Rajput

Reputation: 441

Try this 

update Customers,(SELECT @n := 0) m set ContactName =concat('Name',@n := @n + 1);

Upvotes: 3

flexr
flexr

Reputation: 1

I'm not too versed in SQL but you should be able to use a WHILE loop to simulate a FOR loop. Generally,

DECLARE @cnt INT = 0;

WHILE @cnt < cnt_total
BEGIN
   {...statements...}
   SET @cnt = @cnt + 1;
END;

where 'cnt_total' is the total number of loops you want perform and 'statements' is what actions you want to perform during each iteration.. should be able to adapt this to your problem.

Upvotes: 0

Related Questions