CODEWITHSUNDEEP
mysql
Shift 'n Tab
Shift 'n Tab

Reputation: 9443

How to bulk update a records with value incremented

I have over 1100+ records in my people (table) and i would like to filter those who are under a specific place (table) record and would update a newly inserted column value named people_identifier_num of people table.

Place table

id   name          desc
1    Place 1       some description.
2    Place 2       some description..
3    Place 3       some description...

People table

id     name         place_id       people_identifier_num
1      People 1     1              null     
2      People 2     1              null
3      People 3     2              null
4      People 4     2              null
5      People 5     2              null
6      People 6     2              null
7      People 7     3              null
...    ...          ...            ....
1100   People 1100  2              null

Now i would like to assign a pin (people_identifier_num) to every person that belongs to a particular place which begin with value 1 and so on..

E.g. 

id     name         place_id       people_identifier_num
3      People 3     2              1
4      People 4     2              2
5      People 5     2              3
6      People 6     2              4
...    ...          ...            ....

E.g.

id     name         place_id       people_identifier_num
1      People 1     1              1
2      People 2     1              2
...    ...          ...            ....

My current solution is to filter people by place and sort it by name alphabetically. However how can we write a SQL script that loop each filtered record and assign the pin value starting from 1 and so on?

Upvotes: 1

Views: 27

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You can use rank query to assign pin no. to each people according to their place 

update people 
join (
    select p.id,place_id,name,
    case when @place = place_id then @row:= @row +1 else @row:= 1 end rank,
    @place:= place_id
    from (
      select * from people,(select @place:=null,@row:=1) t order by place_id,name
    ) p  
) p1
using(place_id,name)
set people_identifier_num = p1.rank;

Demo

Upvotes: 1

Related Questions