Assign identical ID to duplicates in SQL server

问题:

I would like to create an update query that would assign incremental IDs to values in my table. However, duplicate values should receive the same ID.

MyTable:

pk  Word   ID  
1   Dummy   null  
2   Dummy   null
3   dummy   null  
4   dummy   null  
5   Hello   null  
6   Hello   null  
7   Test7   null  

Expected outcome:

pk  Word   ID  
1   Dummy   1  
2   Dummy   1  
3   dummy   2  
4   dummy   2  
5   Hello   3  
6   Hello   3  
7   Test7   4  

Thanks in advance!

回答1:

You can create a table with an auto increment id field an the word.

MySQL:

CREATE TABLE secondTable (
id  int NOT NULL AUTO_INCREMENT,
word  varchar(255) NULL,
PRIMARY KEY (id)
);

MSSQL:

CREATE TABLE [secondTable] (
[id] int NOT NULL IDENTITY(1,1) ,
[word] varchar NULL ,
PRIMARY KEY ([id])
)

After that you insert the distinct values of word in your secondTable.

INSERT INTO secondTable (word) SELECT distinct word FROM MyTable;

At last you can update your table with the grouping IDs:

UPDATE MyTable 
SET ID = (SELECT id from secondTable where MyTable.word = secondTable.word)