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-15T12:27:24.397

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 '20 at 12:27

answered Jun 15 '20 at 08:48

Nick

138,499
22
57
95

2

Watch out for the user variables here... Since *[The order of evaluation for expressions involving user variables is undefined](https://dev.mysql.com/doc/refman/8.0/en/user-variables.html)*, 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. – GMB Jun 15 '20 at 08:55
@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. – Nick Jun 15 '20 at 09:00
While this gives me wanted result in select query how to add this into update query to update actual rows? . – ikiK Jun 15 '20 at 09:02
All I could think of is taking the result and making a update query of each result row with editor on computer. – ikiK Jun 15 '20 at 09:10
1

@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 Jun 15 '20 at 09:11
I used this select result and manipulate it on computer to make update query for each row then run on DB. I am reading this discussion and will just note that on 1000+ data rows all results seem right. Ill mark it as answer as it seems right and it is for my DB version. – ikiK Jun 15 '20 at 09:33
@ikiK . . . "seem" is the operator word. MySQL explicitly does not guarantee that the results are correct. Sometimes they will be. Sometimes they won't be. – Gordon Linoff Jun 15 '20 at 11:07
@GordonLinoff I understand the problem and it is bothering me, but seems like I have no other chose currently then to trust it unfortunately, as I don't have MySQL 8. Or looks like I can do this in excel and then import those numbers additionally or do whole import of data again. – ikiK Jun 15 '20 at 11:20
@ikiK . . . Actually, there are ways to address the problem, but you accepted this answer so there is no incentive to provide a more sustainable answer. – Gordon Linoff Jun 15 '20 at 12:04
Wow, you go away for a couple of hours and there's been a complete firestorm. @GMB you have used this identical structure in other answers e.g. https://stackoverflow.com/questions/58186125/how-to-show-rows-in-packs-of-three-of-two-tables-in-mysql/58186410#58186410 – Nick Jun 15 '20 at 12:19
@ikiK I've updated the answer with a query which can be used to update and has *no* variables so will be reliable. Sorry about all the drama, I had to go away for a while so wasn't able to respond earlier. – Nick Jun 15 '20 at 12:28
@Nick: ah ah, that's what happens when MySQL user variables come in the picture (see [this other discussion](https://stackoverflow.com/a/62354347/10676716) for another example of such "firestorm"- yet on a much simpler case!). Yes I did use that before, but, the way I understood the documentation since then, I wouldn't use it that way anymore. – GMB Jun 15 '20 at 12:29
@GMB well there isn't a variable to be seen any more... – Nick Jun 15 '20 at 12:30
I see no drama, just educational discussion, and I appreciate all the time given by all to this. Thanks for all replays. – ikiK Jun 15 '20 at 12:42
@Nick: +1 for providing an alternative solution. You might want to have a look at my answer, that provides a solution based on user variables. – GMB Jun 15 '20 at 13:00

GMB · Answer 2 · 2020-06-15T12:58:58.957

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.

@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! — Nick, Jun 15 '20 at 13:05

Mysql update all rows value with count of same table column

2 Answers2