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,

Here you go: https://tmcr143963.sugarondemand.com

@matthewpoer
Copy link
Author

Will, I reviewed this morning and couldn't find any dupes, but the customer said he saw some. We're pushing back to get IDs so we can compare. Thanks for your help, please hold the case open for a day or so until we can be sure that there are or aren't dupes left.

@wfeliciano
Copy link

Hey Matthew,

I do see a good amount of duplicates. The reason is, the queries that I ran yesterday to update duplicates were only unique to meetings with the same name and date_start. I based that off of your queries. As of now, I still show no duplicate meetings with the same name and start date. However, when I run a search for just meetings with the same name, it returns 309 meetings.

Please review, this is likely what the customer is seeing. The next step, if the customer advises that these duplicates should be removed, would be for me to run the same queries again without the date_start field.

select count(*) as duplicate_count,name from meetings where deleted='0' group by name having duplicate_count > 1 order by duplicate_count desc;

duplicate_count name
146 House Session
145 TIM AND TONY MEETING
123 HOLD - Break
107 Camelot MI Call
75 Copy: Weekly Check In and Conference Call
68 Altria Team Meeting
54 Meetings status - sent to Tim on his meetings
54 Hold House Energy and Tech
54 Weekly MI Legislative Check-in Call - 866-629-7512
54 Copy: MLC Scheduling Meeting
54 Weekly MI Call w/Brendan - 866-629-7512, Code: 52
53 Copy: Camelot MI Call
53 House Session**
53 ALTRIA CALL
52 TW/LC Meet
52 MLC Scheduling Meeting
51 UNISYS CONFERENCE CALL
51 Update dashboard for tomorrow's meeting
50 Write the Capital Update
48 Check tomorrow's House agenda for dashboard bills
47 Check tomorrow's Senate agenda for dashboard bills
47 EBAY/STUBHUB WEEKLY CALL
46 CAMI & TIM'S WEEKLY MTG
45 Copy: TW/LC Meet
45 Copy: Tim & Liz weekly meeting
44 Copy: MI team call
43 Weekly Check In and Conference Call
43 Copy: Business Mtg Prep w/ LuAnn
42 Business Mtg Prep w/ LuAnn
42 MLC Weekly Business Meeting
42 Senate Session**
41 Copy: WEEKLY TIM/CIA MEETING
40 Copy: Cia & Brendan Meeting
40 LIZ & TIM'S WEEKLY MEETING
38 Lunch
34 GL-ITC Updates
33 Copy: MLC Weekly Business Meeting
31 Copy: GL-ITC Updates
28 Senate Session
27 NASPO Marketing Future Meetings
26 ORACLE CALL
26 TRAVEL
26 Copy: Government Affair Consultant, Tim Ward - ML
25 MI team call - every other week now (and now with
25 Copy: MI team weekly call
24 Copy: MI team call - every other week now (and now
21 Copy: Oracle MI team call
17 New Year's Eve
16 Valentine's Day
16 Independence Day
16 Groundhog Day
16 Christmas Day
16 Flag Day
16 New Year's Day
16 Veteran's Day
16 Halloween
16 Lincoln's Birthday
16 Saint Patrick's Day
16 Christmas Eve
16 Copy: Weekly Team Call
15 Alicia
14 Check dates for MJA, MLTA & MiSEPS monthly meeting
13 House Energy and Tech Committee
13 MLTA LEGISLATIVE STEERING CON CALL
13 Copy: MTBOA Board of Directors Phone Conference Me
13 MLTA Board Meeting
12 MCSSA Legislative Committee Meeting
11 Senate Appropriations Committee
11 Cherryland Conference Call
11 Copy: NMU / Big Bay project update
10 Brendan's Birthday
10 Rep. Kurt Damrow's birthday
10 Charlotte's B-day
10 Rep. George Darany's birthday
10 Dutch-American Heritage Day
10 Michelle's Birthday (formerly Speaker's Office) no
10 Rep. Peter MacGregor's birthday
10 Kevin's B-day
10 Rep. David Rutledge's birthday
10 JIM ALEXANDER B-DAY
10 Sen. Steve Bieda's birthday
10 EVAN'S BIRTHDAY
10 GET FLU SHOT
10 Rep. Marilyn Lane's birthday
10 Rep. Aric Nesbitt's birthday
10 DAD'S BIRTHDAY
10 Rep. Anthony Forlini's birthday
10 House Families Committee
10 MARY'S BIRTHDAY
10 Rep. Kurt Heise's birthday
10 Rep. Andrea LaFontaine's birthday
10 Rep. Bradford Jacobsen's birthday
10 Anne's Birthday
10 ANN & JIM'S ANNIVERSARY
10 REMINDER - ADD BUDGET WORKSHEET TO DASHBOARD UPDAT
10 Bryan Crenshaw's Birthday
10 Sen. Tonya Schuitmaker's birthday
10 Rep. Ray Franz's birthday
9 TENTATIVE: Senate Session
9 DaVita Log
8 Martin Luther King Day
8 Administrative Professionals Day
8 House Agriculture Committee
8 Thanksgiving Day
8 Memorial Day
8 Senate DHS Appropriations
8 Tax Day
8 Hold for Senate Judiciary
8 Columbus Day
8 Labor Day
8 Mother's Day
8 Easter Day
8 Father's Day
8 Election Day
7 House Appropriations Committee
7 Thinking Time
7 Copy: Appointment with Alicia Bruce at Simplicity
7 Hold
7 Copy: TW/Kristyn Weekly Meeting
7 CAMELOT CALL
7 FW: MLC Weekly Business Meeting
7 Presidents' Day
7 Copy: BOMA Government Affairs Committee
7 TIM & JOSH WEEKLY MEETING
7 Copy: In-Home Care Incentive
7 Amy
6 Copy: Indio Soccer
6 Michelle
6 Copy: CAIR Lobby Update Meeting
6 Leg strength exercise
6 MJA meeting**
5 MTOBA BOARD MEETINGS
5 Drive to Lansing
5 Work on Fundraisers
5 Copy: Hair Appointment
5 SHANELLE
5 Eric Soccer
5 PICK UP DRY CLEANING
5 Do Not Book - Dave in Japan
4 Private Appointment
4 LOG OFF COMPUTER
4 Run
4 Prepare and send out ECN Invoices for Quarterly Pa
4 Senate Tourism and Outdoor Recreation
4 Marybeth
4 Netherlands Trade Mission
4 Copy: Updated: Weekly Team Call
4 House Insurance Committee
4 Copy: GL-ITC Weekly Update
4 Lauren-VBall Practice
4 Copy: Weekly GL-ITC Update
4 House Commerce Committee
4 Copy: MI Call
4 Eric-Soccer
4 House Agriculture Appropriations
4 ELLIPTICAL/STRETCH
4 PAY ECN Quarterly Taxes - State $300 & IRS - Corp.
3 Call Patty
3 Call on meetings
3 GAC
3 State holiday -- state offices closed
3 House DHS appropriations subcommittee
3 MSU Baseball vs Purdue
3 Transportation and Infrastructure
3 MISEPS BOARD MEETING
3 Drop Mya Off
3 Camilla
3 House Natural Resources Committee
3 House Families, Children & Seniors Committee
3 General Government
3 MWQA Board Meeting
3 WEIGHTS/CORE/STRETCH
3 Copy: Cyber Security meeting
3 For DaVita Call
3 Lunch w Katie Carey
3 Lunch w Brad Comment
3 DINNER
3 BIG TEN TOURNEY
3 MSU Baseball vs ILlinois
3 FYI - LuAnn lvg for PT
3 Canceled: MLC Weekly Business Meeting
3 BOMA Dinner
3 Copy: MLC WKLY BUSINESS MTG
3 pick up mya
3 Process Credit Card Quarterly Dues Payments for Da
3 Financial Services
3 House Judiciary
3 House Higher Education Appropriations
3 Copy: Weekly Update Call
3 House DHS Appropriations
3 Copy: ALTRIA TEAM MEETING
3 Senate Families Committee
3 Test
3 CAMELOT CON CALL
2 Rep. Tim Greimel (Altria Check) (Hilary) as
2 Copy: Tigers Game
2 Out of the Office
2 Leave for Wixom
2 TEETH CLEANING
2 WMGI Conference Call
2 KOSITCHEKS
2 Pick Mya UP
2 House Education Committee
2 House Higher Education Appropriations Subcommittee
2 HOLD - Meet w/ Bob, Colleen & Architect
2 MDOC
2 Eric-Soccer Game (All White)
2 Copy: DIRECTV/DISH MI Lobbyist Update Call (3pm ES
2 Copy: Weekly update
2 Senate Agriculture Appropriations
2 Dr. Schafer
2 Senate Higher Education Appropriations
2 Copy: CAIR Lobby Team
2 Lauren-Soccer Practice
2 Copy: Weekly State of MI Update
2 House Democratic Leader Tim Greimel & Floor Leader
2 Children's Trust Fund Gift Committee
2 Senate General Government Appropriations
2 7:45 - LuAnn @ PT
2 EXPLORE TEST FOR LAUREN
2 Brunch
2 FYI - LuAnn @ PT
2 Dan Dundas
2 MACVB Board Meeting
2 Meeting w Stephanie johnson
2 LARA
2 Drive to appointment
2 Detroit ChamberMackinac Policy Conference
2 Senate Economic Development Committee
2 MLC Baseball Tailgate
2 TIM HOLSTON CALL
2 Treasurer Andy Dillon with Don Taylor (Amy) as
2 HOLD JOHN ROBERTS DINNER
2 Yom Kippur
2 Meeting w Derek Bajema
2 House Judiciary Approps
2 Single State Fire Code Meeting
2 Jeb
2 CALL DAN SCRIPPS
2 LOG OFF YOUR COMPUTER BEFORE YOU LEAVE ON THURSDAY
2 Copy: BLGA Election 2010 Wrap-Up
2 Monday 9am Legislative Steering Committee Meeting
2 LOG OFF
2 Call Murley
2 Dennis Muchmore and Dave Murley regarding update o
2 Copy: Shingu Shoko Visit
2 Copy: PGMI Board Meeting
2 Sheldon Tax
2 Senate/House District spreadsheet for Brendan
2 Eric-Crossroads of America Socccer Tournament
2 Mahout Reception (House Republicans)
2 Dr. Israel
2 Copy: Tim, Liz & Josh meet to go over legislative
2 Copy: Ladies Night
2 MACVB Conference Call
2 Dr. Commings
2 Process MMOA PAC Checks
2 [APPR] STANDARD: House Licensing and Regulatory Af
2 Rep. Collene Lamonte Fundraiser (BOMA Plan $200)
2 don't schedule
2 Copy: Agate/MLC Meeting
2 MI Camelot Call
2 Soccer in the Sand-Grand Haven
2 EVAN'S TENNIS CAMP THIS WEEK
2 BRIAN MILLS
2 HOLD FOR CC TOUR - JUNE 3 OR 17 AND AUGUST 12
2 Jon Cool
2 TENTATIVE: Rep. Lane re: political update (Frank)
2 Copy: Estimated taxes due
2 Call Lindsay Chalmers
2 CLIENT ALLOCATION REVIEW
2 House Judiciary Committee
2 Drive time
2 Copy: E-Limbs Software install
2 REPUBLICAN CONFERENCE ON MACKINAC ISLAND
2 [Sen-HealthPolicy] Senate Committee on Health Poli
2 Senate Judiciary Committee
2 MEETING WITH TIM
2 Haircut & Highlight
2
2 Copy: SugarCRM Discussion
2 DAVE MURLEY
2 lobby team meeting
2 Lunch w Dan Dundas
2 [MICH] STANDARD: House Michigan Competitiveness St
2 Senate Agriculture Committee
2 Meeting with Camelot
2 Copy: CAIR Lobby Team Meeting
2 Transportation and Infrastructure, Joint Meeting w
2 Copy: GAC
2 Work on Strategic Plans
2 Senate-General Government
2 NICOLE NYSTROM
2 Copy: TONY & TIM WEEKLY MEETING
2 House Oversight Committee
2 Copy: Business Meeting
2 POAM STRATEGIC PLAN
2 Canceled: MLC Scheduling Meeting
2 HOUSE OVERSIGHT
2 House Tourism Committee
2 Kathy DeGeest
2 Copy: State of Michigan Client business Planning s
2 [ENER] STANDARD: House Standing Committee Meeting
2 Maureen Saxton
2 Drive to Grand Rapids
2 Senate Reforms, Restructuring and Reinventing
2 MIKE SLAUGHTER
2 MLC Pre-meeting
2 HOUSE TAX POLICY

@matthewpoer
Copy link
Author

Will, I think you are/were right the first time. The definition of these duplicates is same name + same start date/time. We've pushed back to the customer to show us real duplicates using that definition in the test instance. They had a guy out of town late last week, so we're just waiting on them to approve before we go live.

@wfeliciano
Copy link

Matthew,

Sounds good. Keep us posted. I'll be checking in periodically.

Thanks

@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