[mythtv-users] Failed database update 1327->1328

Bill Meek keemllib at gmail.com
Mon Jun 23 13:47:36 UTC 2014


On 06/23/2014 02:56 AM, Mike Holden wrote:
> Gary Buhrmaster wrote:
>> Those that want to do check to see if they are going to
>> have issues can try to run a sql query of the form (only
>> written, never actually run/tested, and not really efficient):
>>    select * from record as p1 where (select count(*) from record as
>> p2
>>      where p1.chanid = p2.chanid and p1.starttime = p2.starttime and
>>      p1.startdate = p2.startdate and p1.title = p2.title and p1.type
>> =
>> p2.type) > 1;
>
> Probably academic, but likely more efficient, and a useful lesson
> for anyone interested in SQL:
>
> SELECT chanid, starttime, startdate, title, COUNT(*)
> FROM record
> GROUP BY chanid, starttime, startdate, title
> HAVING COUNT(*) > 1
> ORDER BY 1, 2, 3, 4;
>
>
> For the record, my database returned zero rows!

Changed the query to reflect the comments that followed this
post and added it to the 0.28 Release Notes (at least for now.)

Hope this is correct:

SELECT chanid,starttime,startdate,title,type,COUNT(*) FROM record
GROUP BY chanid,starttime,startdate,title,type
HAVING COUNT(*) > 1 ORDER BY 1,2,3,4,5;

-- 
Bill


More information about the mythtv-users mailing list