Mysql update all rows value with count of same table column

Question

I have following table with data:

| predp_id | strp_ID | predp_nas |
| -------- | ------- | --------- |
| 1        | 1       |   null    |
| 2        | 1       |   null    |
| 3        | 1       |   null    |
| 4        | 2       |   null    |
| 5        | 2       |   null    |
| 6        | 3       |   null    |

predp_nas column should be count of strp_ID column + 1 for same strp_ID on every row.

I am currently using next query to achieve this on every new insert:

INSERT INTO PREDMETIP
    (`strp_ID`, `predp_nas`)
VALUES(
 1, 
 (SELECT counter + 1 FROM (SELECT COUNT(strp_ID) counter FROM PREDMETIP WHERE strp_ID = '1') t)
);

This gives me:

| predp_id | strp_ID | predp_nas |
| -------- | ------- | --------- |
| 1        | 1       |   null    |
| 2        | 1       |   null    |
| 3        | 1       |   null    |
| 4        | 2       |   null    |
| 5        | 2       |   null    |
| 6        | 3       |   null    |
| 7        | 1       |     4     |

But now I have imported large amount of data and I need to update all predp_nas fields at once to give me result:

| predp_id | strp_ID | predp_nas |
| -------- | ------- | --------- |
| 1        | 1       |     1     |
| 2        | 1       |     2     |
| 3        | 1       |     3     |
| 4        | 2       |     1     |
| 5        | 2       |     2     |
| 6        | 3       |     1     |
| 7        | 1       |     4     |

I have DB fiddle with insert query View on DB Fiddle , I am having trouble understanding how to write query for same thing but to update all fields at once. Any help is appreciated.

Answer 1

What you're looking for is ROW_NUMBER() (if you're using MySQL 8+), but since your fiddle is on MySQL 5.7 I'm assuming that's your version and so you can emulate it by counting the number of rows for a given strp_ID that have a lower predp_id and using that to update the table:

UPDATE PREDMETIP p1
JOIN (
  SELECT p1.predp_id,
       COUNT(p2.predp_id) + 1 AS rn
  FROM PREDMETIP p1
  LEFT JOIN PREDMETIP p2 ON p2.strp_ID = p1.strp_ID AND p2.predp_id < p1.predp_id
  GROUP BY p1.predp_id
) p2 ON p1.predp_id = p2.predp_id
SET p1.predp_nas = p2.rn
;
SELECT *
FROM PREDMETIP 

Output after update:

predp_id    strp_ID     predp_nas
1           1           1
2           1           2
3           1           3
4           2           1
5           2           2
6           3           1
7           1           4

Answer 2

You seeem to be looking for an update query. If you are running MySQL 8.0, you can do this with row_number():

update predmetip p
inner join (
    select p.*, row_number() over(partition by predp_id order by strp_id) rn
    from predmetip p
) p1 on p1.predp_id = p.predp_id and p1.strp_id = p.strp_id
set p.predp_nas = p1.rn

---

On the other hand, if you are running a MySQL 5.x version, then one option is to use correlated subqueries, as demonstrated in Nick's answer. This works fine - and I upvoted Nick's answer - but the performance tends to quickly degrade when the volume of data gets larger, because you need to scan the table for each and every row in the resultset.

You can do this with user variables, but it's is tricky: since, as explained in the documentation, the order of evaluation of expressions in the select clause is undefined, we need to evaluate and assign in the same expression ; case comes handy for this. Another important thing is that we need to order the rows in a subquery before variables come into play.

You would write the select statement as follows:

set @rn := 0, @strp_id = '';
select 
    predp_id,
    strp_id,
    @rn := case 
        when @strp_id  = strp_id then @rn + 1 -- read
        when @strp_id := strp_id then 1       -- assign
    end as predp_nas
from (
      select * 
      from predmetip
      order by strp_id, predp_id
) t

You can then turn it to an update:

set @rn := 0, @strp_id = '';
update predmetip p
inner join (
    select 
        predp_id,
        strp_id,
        @rn := case 
            when @strp_id  = strp_id then @rn + 1
            when @strp_id := strp_id then 1
        end as predp_nas
    from (
          select * 
          from predmetip
          order by strp_id, predp_id
    ) t
) p1 on p1.predp_id = p.predp_id and p1.strp_id = p.strp_id
set p.predp_nas = p1.predp_nas;

Demo on DB Fiddle (with credits to Nick for creating it in the first place).

To read more about user variables and their tricks, I recommend this excellent answer by Madhur Bhaiya, which also contains another interesting blog link.