create temporary table user_date_unique select a.id from ( select user_id, date_stamp from user_date group by user_id,date_stamp,deleted having count(*) > 1 ) as tmp, user_date as a WHERE a.user_id = tmp.user_id AND a.date_stamp = tmp.date_stamp AND a.deleted = 1;
delete from user_date where id in (select * from user_date_unique);
drop table user_date_unique;
create temporary table user_date_unique select id from punch_control where deleted = 1 AND user_date_id in ( select a.id from ( select user_id, date_stamp from user_date group by user_id,date_stamp,deleted having count(*) > 1 ) as tmp, user_date as a WHERE a.user_id = tmp.user_id AND a.date_stamp = tmp.date_stamp AND a.deleted = 0 );
delete from punch_control where id in (select * from user_date_unique);
drop table user_date_unique;
create temporary table user_date_unique  select a.id from ( select user_id, date_stamp from user_date group by user_id,date_stamp,deleted having count(*) > 1 ) as tmp, user_date as a WHERE a.user_id = tmp.user_id AND a.date_stamp = tmp.date_stamp AND a.deleted = 0 AND (select count(*) from punch_control as x where a.id = x.user_date_id AND x.deleted = 0) = 0;
delete from user_date where id in ( select * from user_date_unique );
drop table user_date_unique;
create temporary table user_date_unique  select a.id from ( select user_id, date_stamp from user_date group by user_id,date_stamp,deleted having count(*) > 1 ) as tmp, user_date as a WHERE a.user_id = tmp.user_id AND a.date_stamp = tmp.date_stamp AND a.deleted = 0 AND a.id not in (select min(id) from user_date group by user_id,date_stamp,deleted having count(*) > 1);
delete from user_date where id in ( select * from user_date_unique );
drop table user_date_unique;
create unique index user_date_user_id_user_date_deleted on user_date(user_id,date_stamp,deleted);