Skip to content

Instantly share code, notes, and snippets.

@matthewpoer
Created June 11, 2013 04:18
Show Gist options
  • Save matthewpoer/5754456 to your computer and use it in GitHub Desktop.
Save matthewpoer/5754456 to your computer and use it in GitHub Desktop.
System had some dramatic Meeting record duplication after using the Outlook plugin plus and an Exchange integration at the same time. Luckily, the duplicate records seem to match exactly on the name and date_start, so we have something solid to merge off of.
-- create a temp table to house dup'd ids
create table `meetings_dup_ids` (
`id` varchar(36) NOT NULL,
PRIMARY KEY (`id`)
);
-- copy the dup IDs into the temp table...
insert into meetings_dup_ids(id)
select distinct(one.id) from meetings one
join meetings two on one.name = two.name and one.date_start=two.date_start
and two.deleted = 0
where one.deleted = 0;
-- select duplicated groups with min/max start date and a total 'dupes'
-- select id,name,
-- max(date_start) as max,min(date_start) as min,
-- count(*) as dups from meetings
-- where meetings.deleted = 0
-- group by name
-- order by dups desc;
-- the wrapup, showing only dups with matching datestamps
-- select * from
-- (
-- select id,name,
-- max(date_start) as max,min(date_start) as min,
-- count(*) as dups from meetings
-- where meetings.deleted = 0
-- group by name
-- order by dups desc
-- ) as sub
-- where sub.max = sub.min and sub.dups > 1;
-- the re-wrapped update sets deleted=2, which means 'save me!'
update meetings set deleted=2 where id in
(
select id from
(
select id,name,
max(date_start) as max,min(date_start) as min,
count(*) as dups from meetings
where meetings.deleted = 0
group by name
order by dups desc
) as sub
where sub.max = sub.min and sub.dups > 1
);
-- the re-wrapped update that actually soft-deletes things...
update meetings
set deleted=1 where deleted=0 and id in
(
select id from meetings_dup_ids
);
-- and re-activate the '2' group
update meetings set deleted=0 where deleted=2;
-- and see what we have now
-- select * from meetings where deleted = 0;
-- drop that temp table
drop table `meetings_dup_ids`;
@wfeliciano
Copy link

Matthew,

I ran the update queries. Please confirm with the customer and Tim when everything is verified.

Thanks and take care,

Will

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment