mysql - 如何在 SQL 中更新每组的最大行数

Question

我刚刚将“默认”列添加到我的数据库中。我尝试根据每个 accountId 的最新“addDate”将默认值设置为“1”。

+----+-----------+--------------------+--------+
| id | accountId |      addDate       | default|
+----+-----------+--------------------+--------+
| 1  | 45        |2012-02-29 08:41:59 |        |
| 2  | 55        |2012-03-29 08:41:59 |        |
| 3  | 45        |2012-04-29 08:41:59 |        |
| 4  | 55        |2012-05-29 08:41:59 |        |
| 5  | 60        |2012-05-29 08:41:59 |        |
+----+-----------+--------------------+--------+

我发现我能够通过使用=>来隔离正确的行

select * from tble1 
where addDate = (select max(addDate) from tble1 as sl where sl.accountId = tble1.accountId);

我需要能够运行一个更新，根据最新的“addDate”，每个“accountId”仅将“默认”列设置为“1”1 次。

Answer 1

试试这个

 UPdate Table1 
 SET `default` = 1 
 where addDate in (select * from (
                               select max(addDate) from table1 as sl group by accountId)t
                                 )

DEMO HERE