SQL Updating Values of Column for Each Unique Value in Separate Column (without a loop)

TripleCute 0
Viewed 28

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:

Type ID
x ID1
x ID1
y ID2
y ID2
z ID3
z ID3

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?

common-table-expression sql-update mysql sql
1 Answers

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

type id
x ID1
x ID1
y ID2
y ID2
z ID3
z ID3

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

Mihe0
Related