Update column1 with identical column2

I have this:

ID    Num
1     KP2开发者_如何学编程
2     KP3
3     KP2
4     KP3
5     KP3

I need to get this:

   ID    Num
    1     KP2
    2     KP3
    1     KP2
    2     KP3
    2     KP3

I tried this:

  UPDATE t
  SET ID = t2.ID
  FROM table t JOIN table t2 ON t.Num = t2.Num

This does not work though. Nothing is changes It says that rows were updated, but IDS are still the same.

How about this:

UPDATE t
SET ID = (SELECT MIN(ID) FROM t AS t2 WHERE t.Num = t2.Num)

Having said that, the name "ID" sort of suggests primary key. Are you sure this is what you want?

Similar to SQLMenace's answer but avoiding the JOIN

;WITH T
     AS (SELECT *,
                dense_rank() OVER(ORDER BY num) AS Number
         FROM  YourTable)
UPDATE T
SET    id = Number

One way by using a windowing function

UPDATE t
 SET t.id =x.number
 FROM (SELECT num,dense_rank() OVER(ORDER BY num) AS Number
 FROM table) x
 JOIN table t ON t.num = x.num

example you can run

CREATE TABLE #test (id INT, num VARCHAR(100))
INSERT #test VALUES(1,'KP2')
INSERT #test VALUES(2,'KP3')
INSERT #test VALUES(3,'KP2')
INSERT #test VALUES(4,'KP3')
INSERT #test VALUES(5,'KP3')



 UPDATE t
 SET t.id =x.number
 FROM (SELECT num,dense_rank() OVER(ORDER BY num) AS Number
 FROM #test) x
 JOIN #test t ON t.num = x.num

 SELECT * FROM #test



1   KP2
2   KP3
1   KP2
2   KP3
2   KP3

Try this:

 UPDATE t   
 SET ID = t2.ID   
 FROM table t 
 JOIN (select min(ID), num from table group by num)t2 
    ON t.Num = t2.Num