0

I have a 10k rows db, each one rappresenting a contract with a name on it, there are different contract with the same name and I would like to add a column with the same number for the same name. To select all the names I implemented this:

SELECT cliente FROM tbl_c WHERE id IN 
(SELECT MAX(id) FROM tbl_c GROUP BY name)

Now I would like to loop in something like this

SET @num:=10000
FOR EACH Element IN *Selection Above*
UPDATE tbl_c
SET num_column=@num WHERE name=Element
@num:=@num+1

Is it possible to do something like this with mysql?

GokuGunZ
  • 16
  • 2

1 Answers1

2

You can do it in one query with a self-join:

UPDATE tbl_c AS t1
JOIN (
    SELECT MAX(id)
    FROM tbl_c
    GROUP BY name
) AS t2 ON t1.id = t2.id
CROSS JOIN (SELECT @num := 999) AS vars
SET num_column = (@num := @num + 1)
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • I tried this but doesn't do the things I want: I would like to give all the column with the same name the same number – GokuGunZ May 05 '22 at 17:36
  • Are you using MySQL 8.0. You could use the `RANK()` window function for that. – Barmar May 05 '22 at 18:10
  • See the linked question, it has answers showing how to calculate dense rank in MySQL. Join with a subquery that does that, and then assign the appropriate rank. – Barmar May 05 '22 at 18:12