Update column based on other column with concat by underscore and manage duplicate value MySQL query for mysql 5.7.27
akina: You need to emulate ROW_NUMBER(). There are 2 general ways:


Subquery which uses 2 table references (see, for example, Order by is ignored if inside a derived table of a update, with the correction that the subquery must be moved to main query's UPDATE clause); and
Subquery which uses user-defined variables (see, for example, query to replace text with sequential numbers).