[mythtv-users] Auto-delete duplicate recordings
Tony Lill
ajlill at ajlc.waterloo.on.ca
Fri Dec 20 18:21:13 UTC 2013
On 12/17/2013 10:07 AM, George Nassas wrote:
> On Dec 17, 2013, at 12:56 AM, Jacob Strandlien <kepesk at gmail.com>
> wrote:
>
>> Hello! I have been trying to find a way to do this for a couple
>> of days now with no luck.
>>
>> I have a large archive of recordings on a well-used mythbox
>> (0.27) and I know I have accumulated a lot of duplicates. I need
>> to free some space, but going through and manually getting rid of
>> the duplicates would be a monumental task.
>>
>> Is there any mechanism or script available to track down these
>> duplicates, and preferably delete all but the newest recording?
>> I've searched the web and poked at my interface with no luck.
>
> I think your only recourse is a direct update to the database but
> the main question is what counts as a duplicate? A good identifier
> is the column programid and using that I whipped up this little bit
> of sql:
>
> update recorded r1 set recgroup = 'Duplicates' where r1.programid
> != '' and substr(r1.programid, 11) != '0000' and exists (select 1
> from recorded r2 where r1.starttime < r2.starttime and r1.programid
> = r2.programid);
>
I tried that, but it didn't work. Can't update a table used in a from
statement. The following modification does, though.
create temporary table duplicate_recorded
select programid, chanid, starttime from recorded r1
where r1.programid != ''
and substr(r1.programid, 11) != '0000'
and exists (select 1
from recorded r2
where r1.starttime < r2.starttime
and r1.programid = r2.programid);
update recorded
inner join duplicate_recorded on
recorded.chanid = duplicate_recorded.chanid
and recorded.starttime = duplicate_recorded.starttime
set recorded.recgroup = "Duplicates";
drop table duplicate_recorded;
--
Tony Lill, OCT, Tony.Lill at AJLC.Waterloo.ON.CA
President, A. J. Lill Consultants (519) 650 0660
539 Grand Valley Dr., Cambridge, Ont. N3H 2S2 (519) 241 2461
--------------- http://www.ajlc.waterloo.on.ca/ ----------------
More information about the mythtv-users
mailing list