Update on row_number, over partitioned (alternative) in mysql

November 21, 2021

I have a table mmdocpositions where I put documents and positions. Positions have been broken and now there are values 2 in each document. It should be Document1 1, Document1 2, Document1 3, Document2 1, Document2 2, etc… But now it is like Document1 2, Document1 2, Document1 2, etc…

I’ve managed to write SQL script which selects the right result:

set @row_number := 0;
SELECT *
from
(SELECT 
    @row_number:=CASE
        WHEN @document_nr = document 
            THEN @row_number + 1
        ELSE 1
    END AS num,
    @document_nr:=document
FROM
    mmdocpositions WHERE document IN (SELECT document FROM mmdocpositions where POSITION='2'  GROUP BY document,POSITION having COUNT(*)>1) ORDER BY document)x

num	@document_nr:=document
1	CE21100044
2	CE21100044
3	CE21100044
4	CE21100044
1	CE21100046
2	CE21100046
3	CE21100046
4	CE21100046
5	CE21100046
6	CE21100046
1	DA21100419
2	DA21100419
3	DA21100419
4	DA21100419
1	DA21100422
2	DA21100422
3	DA21100422
4	DA21100422
5	DA21100422
6	DA21100422
7	DA21100422
8	DA21100422
9	DA21100422
10	DA21100422
11	DA21100422
12	DA21100422
13	DA21100422
14	DA21100422
15	DA21100422
16	DA21100422
17	DA21100422

I used this workaround since in MYSQL version there is no row_number and OVER BY PARTITION. Now I’ve tried to put this into an UPDATE statement:

    set @row_number := 0;
    UPDATE mmdocpositions SET POSITION=x.num
FROM
    (SELECT 
        @row_number:=CASE
            WHEN @document_nr = document 
                THEN @row_number + 1
            ELSE 1
        END AS num,
        @document_nr:=document
    FROM
        mmdocpositions WHERE document IN (SELECT document FROM mmdocpositions where POSITION='2'  GROUP BY document,POSITION having COUNT(*)>1) ORDER BY document)x

I got SQL syntax error in HEIDISQL. I’ve tried to rewrite the code but couldn’t make it work. I was wondering whether it’s even possible to do it that way or I will have to write the procedure. Please help me out hackers!

>Solution :

Your update command should look like this.

you need to join the table then the new rownumber

i joined both tables ON mmdocpositions.id = x.id, as i know nothing about your table, so you must change that so that mysql will connect the correct rows

set @row_number := 0;
UPDATE mmdocpositions 
INNER JOIN     (SELECT 
    @row_number:=CASE
        WHEN @document_nr = document 
            THEN @row_number + 1
        ELSE 1
    END AS num,
    @document_nr:=document
FROM
    mmdocpositions WHERE document IN (SELECT document FROM mmdocpositions where POSITION='2'  GROUP BY document,POSITION having COUNT(*)>1) ORDER BY document)x
    ON mmdocpositions.id = x.id
SET POSITION=x.num