Mysql update column on first occurrence of each username -

i'm trying mark first occurrence per day of each username. have concerning people marked dupe = 1. first day should set dupe = 2.

like in layman's terms, if first entry of username day, mark column = 2. each day.

based on question https://stackoverflow.com/a/12065956/1811162 can make this

select * (      select * table dupe=1 order date desc      ) x group date 

which returns 1 member of each duplicate i'm looking for, i'd set 1 = 2. having trouble making update statement. or work update statement? want first member set.

the result want - select username, dupe dupe!= 0;

day 1
bob - 2
kathy - 2
bob - 1
kathy - 1
kathy - 1

day 2
kathy - 2
kathy - 1
bob - 2
kathy - 1

what tried is

update table set dupeflag=2 ( select * from (     select * table dupeflag=1 order date desc     ) x group date ) 

but no luck. wrong

i went new flag column this, plus had benefit of helping other question here.

demo schema setup

create table table1 (     id int auto_increment primary key,     username varchar(30) not null,     `date` date not null,     dupeflag int null, --  <---- new flag column, nullable, ignored on inserts below     firstflag int null --  <-- first dupe day? 2=yes, ignored on inserts below );  insert table1 (username,`date`) values ('john','2015-01-01'); insert table1 (username,`date`) values ('kim','2015-01-01'); insert table1 (username,`date`) values ('john','2015-01-01'); insert table1 (username,`date`) values ('john','2015-02-01'); insert table1 (username,`date`) values ('john','2015-03-01'); insert table1 (username,`date`) values ('john','2015-03-01'); insert table1 (username,`date`) values ('kim','2015-01-01'); insert table1 (username,`date`) values ('kim','2015-02-01'); 

update statement, set dupes , first day based on pk id

update table1 t1 join  (   select username,`date`,count(*) thecount,min(id) minforgroup     table1     group username,`date`     having thecount>1 ) inr on inr.username=t1.username , inr.`date`=t1.`date` set dupeflag=1, firstflag=if(id=inr.minforgroup,2,666);   select * table1; +----+----------+------------+----------+-----------+ | id | username | date       | dupeflag | firstflag | +----+----------+------------+----------+-----------+ |  1 | john     | 2015-01-01 |        1 |         2 | |  2 | kim      | 2015-01-01 |        1 |         2 | |  3 | john     | 2015-01-01 |        1 |       666 | |  4 | john     | 2015-02-01 |     null |      null | |  5 | john     | 2015-03-01 |        1 |         2 | |  6 | john     | 2015-03-01 |        1 |       666 | |  7 | kim      | 2015-01-01 |        1 |       666 | |  8 | kim      | 2015-02-01 |     null |      null | +----+----------+------------+----------+-----------+ 8 rows in set (0.00 sec)