將子查詢的值儲存在更新中以供以後使用

試圖計算價格變化的百分比。下面的查詢有效，但希望有一種方法我不必先呼叫相同的子查詢？除非我弄錯了，否則我認為 WITH 在這裡不起作用。

update changes rt1
   set change = coalesce((rt1.price - (SELECT 
       price
     FROM clothing_price
     WHERE changes.id = clothing_price.id AND clothing_price.timestamp > NOW() - INTERVAL '24 HOURS'
     ORDER BY timestamp ASC
     LIMIT 1) / (SELECT 
       price
     FROM clothing_price
     WHERE changes.id = clothing_price.id AND clothing_price.timestamp > NOW() - INTERVAL '24 HOURS'
     ORDER BY timestamp ASC
     LIMIT 1)), 0)
where clothing_type = 1;

（修復無效引用後）查詢燒毀為：

UPDATE changes rt1
SET    change = CASE WHEN (
  SELECT cp.price
  FROM   clothing_price cp
  WHERE  cp.id = rt1.id   -- fixed reference!
  AND    cp.timestamp > now() - interval '24 hours'
  ORDER  BY cp.timestamp
  LIMIT  1) IS NULL THEN 0 ELSE rt1.price - 1 END
WHERE  clothing_type = 1;

除此之外，要“儲存”相關 suquerychanges的結果，必須在USING子句中自聯接到更新表的另一個實例。不過現在好像沒用了……

引用自：https://dba.stackexchange.com/questions/300021