title: SQLite Optimize Query
body: <p>I'm having trouble with SQLite query optimization, it runs fine, but for large tables it takes too much time and I need some help with optimizing it.</p>
  
  <p>Source table:</p>
  
  <pre><code>-------+----------+----------------
  IdMain | IdParent | ColumnToUpdate
  -------+----------+----------------
     1   |          |                
     2   |    1     |     999        &lt;-- IdParent = 1 \
     3   |          |                                  \ 
     4   |    5     |     123                           &gt; DISTINCT ITEMS COUNT = 1
     5   |          |                                  /  IdParent = 1
     6   |    1     |     999        &lt;-- IdParent = 1 /   UPDATE Row with IdMain = IdParent
     7   |    4     |                
     8   |    3     |     456        
  -------+----------+----------------
  </code></pre>
  
  <p>Query to optimize</p>
  
  <pre><code>UPDATE Table
  
  SET ColumnToUpdate = (SELECT DISTINCT ColumnToUpdate
                          FROM Table T
                         WHERE T.ColumnToUpdate IS NOT NULL
                           AND T.IdParent = Table.IdMain)
  
  WHERE Table.ColumnToUpdate IS NULL
  
  AND (SELECT COUNT(*) FROM (SELECT DISTINCT ColumnToUpdate
                               FROM Table T2
                              WHERE T2.ColumnToUpdate IS NOT NULL
                                AND T2.IdParent = Table.IdMain)) = 1 ;
  </code></pre>
  
  <p>Expected table</p>
  
  <pre><code>-------+----------+----------------
  IdMain | IdParent | ColumnToUpdate
  -------+----------+----------------
     1   |          |     999         &lt;-- UPDATE
     2   |    1     |     999        
     3   |          |                
     4   |    5     |     123        
     5   |          |                
     6   |    1     |     999        
     7   |    4     |                
     8   |    3     |     456        
  -------+----------+----------------
  </code></pre>
  
  <p>Pseudo algorithm</p>
  
  <pre><code>FOR Row DO
  BEGIN
  
    IF ColumnToUpdate = NULL THEN
    BEGIN
  
      // count distinct values in ColumnToUpdate
      X = COUNT(DISTINCT(ColumnToUpdate(WITH IdParent = IdMain))
  
      // update row ONLY when number of distinct count equals = 1
      IF X = 1 THEN
        UPDATE(ColumnToUpdate)
  
    END
  
  END
  </code></pre>
  
  <p>I've tried to split it up within the source code (currently Delphi) but it works slow too. Is there any way to speed it up?</p>