Differences

This shows you the differences between two versions of the page.

--- reference:reset_message_folder_counts [2014/11/20 08:54] – paul
+++ reference:reset_message_folder_counts [2018/11/14 10:45] (current) – external edit 127.0.0.1
@@ Line 12: / Line 12: @@
 Then, type
-  with ff as (select folderid,count(*) as cnt, sum(length(msgdata)) as totsize,
+  with ff as (select folderid,count(msgid) as cnt, coalesce(sum(length(msgdata)),0) as totsize,
   sum(case when newflag then 1 else 0 end) as newcnt,
-  sum(case when read and not hold then 0 else 1 end) as unreadcnt,
+  count(case when read and not hold then null else msgid end) as unreadcnt,
-  sum(case when hold then 1 else 0 end) as holdcnt from
+  sum(case when hold then 1 else 0 end) as holdcnt from messages.folders left join
-  messages.foldermessages inner join messages.msgdata using(msgdataid) where deletedtime is null group by folderid)
+  (messages.foldermessages inner join messages.msgdata using(msgdataid)) using(folderid)
-  update messages.folders f set msgcount=ff.cnt, totalsize=totsize, newcount=newcnt, unreadcount=unreadcnt,
+  where deletedtime is null group by folderid)
-  holdcount=holdcnt from ff where f.folderid=ff.folderid;
+  update messages.folders f set msgcount=ff.cnt, totalsize=totsize, newcount=newcnt,
+  unreadcount=unreadcnt, holdcount=holdcnt from ff where f.folderid=ff.folderid;
 (Copy and paste if possible!)
 N.B. VPOP3 **does** need to be stopped while you do this

PSCS Wiki