title: Is it possible to replace sub-queries, used as a scalar value, with CTE?
body: <p>I want to replace a long sub-query that return a scalar value or does not exist with just a short alias, because I place it 3 times to the UPDATE statement. Sub-query takes the last value in <code>UncoveredLoss</code>, if there is one, and the new <code>UncoveredLoss</code> value is calculated in the updated row depending on the last <code>UncoveredLoss</code> value.</p>
  
  <p>It is a non-correlated query, but it is used in <code>SELECT</code> clause, not in the <code>FROM</code> clause. Maybe I should somehow modify the <code>UPDATE</code> statement in the trigger.</p>
  
  <p>The working code:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TRIGGER Result
  
  UPDATE OF Win ON Log
  
  BEGIN
      UPDATE Log
      SET Profit = CASE 
              WHEN NEW.Win = 0
                  THEN - Stake
              WHEN NEW.Win = 1
                  THEN Rate * Stake / 100
              WHEN NEW.Win = 2
                  THEN 0
              END
      WHERE ID = OLD.ID;
  
      UPDATE Log
      SET SumProfit = (
              SELECT Sum(Profit)
              FROM (
                  SELECT StrategyAccountID
                      ,Profit
                  FROM Log
                  WHERE DATE &lt;= NEW.DATE
                  )
              GROUP BY StrategyAccountID
              HAVING StrategyAccountID = NEW.StrategyAccountID
              )
      WHERE ID = NEW.ID;
  
      UPDATE Log
      SET UncoveredLoss = CASE 
              WHEN EXISTS (
                      SELECT UncoveredLoss
                      FROM Log
                      WHERE DATE &lt; NEW.DATE
                          AND StrategyAccountID = NEW.StrategyAccountID
                      ORDER BY DATE DESC LIMIT 1
                      )
                  AND (
                      SELECT UncoveredLoss
                      FROM Log
                      WHERE DATE &lt; NEW.DATE
                          AND StrategyAccountID = NEW.StrategyAccountID
                      ORDER BY DATE DESC LIMIT 1
                      ) + NEW.Profit &lt; 0
                  THEN (
                          SELECT UncoveredLoss
                          FROM Log
                          WHERE DATE &lt; NEW.DATE
                              AND StrategyAccountID = NEW.StrategyAccountID
                          ORDER BY DATE DESC LIMIT 1
                          ) + NEW.Profit
              WHEN NOT EXISTS (
                      SELECT UncoveredLoss
                      FROM Log
                      WHERE DATE &lt; NEW.DATE
                          AND StrategyAccountID = NEW.StrategyAccountID
                      ORDER BY DATE DESC LIMIT 1
                      )
                  AND NEW.Profit &lt; 0
                  THEN NEW.Profit
              ELSE 0
              END
      WHERE ID = NEW.ID;
  END;
  </code></pre>
  
  <p>The simple replacement of the sub-query using CTE not working:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TRIGGER Result
  
  UPDATE OF Win ON Log
  
  BEGIN
      UPDATE Log
      SET Profit = CASE 
              WHEN NEW.Win = 0
                  THEN - Stake
              WHEN NEW.Win = 1
                  THEN Rate * Stake / 100
              WHEN NEW.Win = 2
                  THEN 0
              END
      WHERE ID = OLD.ID;
  
      UPDATE Log
      SET SumProfit = (
              SELECT Sum(Profit)
              FROM (
                  SELECT StrategyAccountID
                      ,Profit
                  FROM Log
                  WHERE DATE &lt;= NEW.DATE
                  )
              GROUP BY StrategyAccountID
              HAVING StrategyAccountID = NEW.StrategyAccountID
              )
      WHERE ID = NEW.ID;
  
      WITH Loss
      AS (
          SELECT UncoveredLoss
          FROM Log
          WHERE DATE &lt; NEW.DATE
              AND StrategyAccountID = NEW.StrategyAccountID
          ORDER BY DATE DESC LIMIT 1
          )
      UPDATE Log
      SET UncoveredLoss = CASE 
              WHEN EXISTS (Loss)
                  AND (Loss) + NEW.Profit &lt; 0
                  THEN (Loss) + NEW.Profit
              WHEN NOT EXISTS (Loss)
                  AND NEW.Profit &lt; 0
                  THEN NEW.Profit
              ELSE 0
              END
      WHERE ID = NEW.ID;
  END;
  </code></pre>
  
  <blockquote>
    <p>Error: near "UPDATE": syntax error</p>
  </blockquote>
  
  <p>It works nicely when I don't replace the sub-query, but when I try to use CTE it fails. I work in sql.el mode in Emacs.</p>