update select

1.WITH AS

WITH tmp AS (
	SELECT
		COALESCE(SUM(a), 0) AS a
		,COALESCE(SUM(CASE WHEN b <> 1 THEN c ELSE 0 END), 0) AS c
		,a_id
	FROM b
	GROUP BY a_id
)
UPDATE a
SET e = tmp.a
	,f = tmp.c
FROM tmp
WHERE a.id = tmp.a_id

2.FROM

UPDATE a
SET e = tmp.a
	,f = tmp.c
	,g = tmp.d
FROM (
	SELECT
		COALESCE(SUM(a), 0) AS a
		,COALESCE(SUM(CASE WHEN b <> 1 THEN c ELSE 0 END), 0) AS c
		,max(d) d
		,a_id
	FROM b
	GROUP BY a_id)tmp
WHERE a.id = tmp.a_id

3.单条更新

UPDATE a
SET e = (SELECT COALESCE(SUM(a), 0) AS a FROM b WHERE a_id = 1)
WHERE id = 1