我有这样的数据库

 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


如何将所有newidmin(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

最佳答案

试试这个UPDATE JOIN

UPDATE
    (
        SELECT sirname,MIN(uniqueid) min_uniqueid
        FROM mytable GROUP BY sirname
    ) A
    INNER JOIN mytable B
    USING (sirname)
SET
    B.newid = A.min_uniqueid
;


为了使此查询非常快速地工作,请确保您在sirname和uniqueid上具有复合索引。

如果没有这样的索引,请事先运行:

ALTER TABLE mytable ADD INDEX sirname_uniqueid_ndx (sirname,uniqueid);


试试看 !!!

关于php - 将组中的列更新为最小值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/9278947/

10-13 07:31