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.

Nick · Accepted Answer · 2020-06-15 12:27:24Z

2

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

edited Jun 15, 2020 at 12:27

answered Jun 15, 2020 at 8:48

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

GMB Over a year ago

Watch out for the user variables here... Since The order of evaluation for expressions involving user variables is undefined, this might not do exactly what you want. There is a technique using a case expression to work around that, I do not remember it well.

Nick Over a year ago

@GMB it's fine as a select. The issue is the ORDER BY will be ignored if you attempt to use it as a subquery for updating, and so it will start numbering again when it encounters the new row. I've made a note to that effect.

ikiK Over a year ago

While this gives me wanted result in select query how to add this into update query to update actual rows? .

ikiK Over a year ago

All I could think of is taking the result and making a update query of each result row with editor on computer.

GMB Over a year ago

@Nick: the first expression in the select clause checks the value of @strp_ID, the second one assigns it. I am quite suspicious about it. It looks a lot like the example given in the docs: For example, there is no guarantee that SELECT @a, @a:=@a+1 evaluates @a first and then performs the assignment.

|

GMB · Accepted Answer · 2020-06-15 12:58:58Z

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.

edited Jun 15, 2020 at 12:58

answered Jun 15, 2020 at 8:49

GMB

224k25 gold badges103 silver badges151 bronze badges

2 Comments

ikiK Over a year ago

Yes I need update, but for lower version of MySQL 5.6.43

Nick Over a year ago

@GMB you were right about the case trick (and I should have remembered it), in the same question Gordon also gives an answer using if to the same effect (and in fact it was Gordon who prompted Madhur to change his code (it originally looked just like mine). So anyway, +1 for fixing my original answer!

Collectives™ on Stack Overflow

Mysql update all rows value with count of same table column

2 Answers 2

15 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

15 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related