Update x column-CodePudding

How to update a specific column in stats table by using/amending the below mentioned script

SELECT
    s.id,
    COUNT(t.val) AS count
FROM stats s
LEFT JOIN
(
    SELECT fir AS val FROM history UNION ALL
    SELECT sec FROM history        UNION ALL
    SELECT thi FROM history        UNION ALL
    SELECT fou FROM history        UNION ALL
    SELECT fif FROM history        UNION ALL
    SELECT six FROM history
) t
    ON s.id = t.val
GROUP BY
    s.id;

CodePudding user response：

According to Postgres documents You can use update query with from statement and use the result in set

UPDATE stats u_s
SET result = tmp_s.count
FROM (
  SELECT
    s.id,
    COUNT(t.val) AS count
  FROM stats s
  LEFT JOIN
  (
    SELECT fir AS val FROM history UNION ALL
    SELECT sec FROM history        UNION ALL
    SELECT thi FROM history        UNION ALL
    SELECT fou FROM history        UNION ALL
    SELECT fif FROM history        UNION ALL
    SELECT six FROM history
  ) t
    ON s.id = t.val
  GROUP BY
    s.id
) tmp_s
WHERE u_s.id = tmp_s.id;