I'm currently writing a query that is grabbing data from a table and I want to update one of the columns in that table. The update needs to be based off the distinct values from one column. For example:

The catch here is that I can't use a loop to do it.

The table has a lot more columns but I'm only sorting it by "Type" and then updating that table and assigning a unique ID based off its "Type"

Any ideas on how to do this?

CodePudding user response：

You can use dense_rank() for this:

CREATE TABLE myTable(type VARCHAR(10), id VARCHAR(10));
INSERT INTO myTable VALUES
  ('x', NULL), ('x', NULL),
  ('y', NULL), ('y', NULL),
  ('z', NULL), ('z', NULL);

UPDATE myTable
  JOIN (SELECT type, dense_rank() OVER (ORDER BY type) AS id FROM myTable) ids 
    ON myTable.type = ids.type
   SET myTable.id = concat('ID', ids.id);

results in

More information about dense_rank() can be found in the documentation