matthewpoer · June 11, 2013 04:18 · wfeliciano · Jun 11, 2013 · matthewpoer · Jun 13, 2013
diff --git a/MLCMI Meeting Cleanup b/MLCMI Meeting Cleanup
 -- 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`;
	-- 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`;