举例说明:
表user
id | name | status |
1 | 张三 | |
2 | 张三 | |
3 | 李四 | |
4 | 李四 |
将同名的第一条记录的status设为:N
如果你打算用下面的方式更新就错了,mysql暂时不支持该方式
update user , tmp set user.status='Y' where id in (select id from user group by name);
方式一:创建临时表更新
create TEMPORARY table tmp select min(id) as id from user group by name;
update user , tmp set user.status='Y' where user.id=tmp.id;DROP TABLE tmp;
方式二:
update user , (select min(id) as id from user group by name) as tmp set user.status='Y' where user.id=tmp.id;