php - 将列更新为组中的最小值

Question

我有这样的数据库

 uniqueid |     name    | newid | sirname
----------+-------------+-------+---------
     A1   |     Adam    |  NULL |  Danny 
     A2   |  Adam (Lee) |  NULL |  Danny 
     A5   |  Adam (Koh) |  NULL |  Danny
     B4   |     Bruce   |  NULL |    CK
     B6   |  Bruce (Lee)|  NULL |    CK 

如何将newid更新为min(unique id)来自group by Sirname

我想要的结果:

 uniqueid |     name    | newid | sirname
----------+-------------+-------+---------
    A1    |     Adam    |   A1  |  Danny
    A2    |  Adam (Lee) |   A1  |  Danny
    A5    |  Adam (Koh) |   A1  |  Danny
    B4    |     Bruce   |   B4  |   CK
    B6    | Bruce (Lee) |   B4  |   CK

Answer 1

试试这个:

UPDATE table a 
SET    new_id = (SELECT MIN(unique_id) 
                 FROM   table b 
                 WHERE  b.sirname = a.sirname)