Using WITH and subset in CASE construction

Question

Greetings to senior colleagues. I got caught up in this thing. It is necessary to substitute the corresponding values in the CASE when the construction depending on the state of the load_date field. The problem is that the second was assigned a value to the mean_v field based on the calculation of the average mean_v obtained for the subsample. how to implement value acquisition and assignment in when then? I have been tried to create alias , with alias () as (), but sql throws syntax error near WITH. I have to assign an average value from the subset where the columns correspond to the current row for each row What the problem? Can I use WITH in CASE?

UPDATE public.table 
set mean_v =
            CASE 
                WHEN table.load_date IS NOT NULL 
                    THEN 
                    DATEDIFF(day, CONVERT(date, table.load_date),CONVERT(date, table.req_date))

                WHEN table.load_date IS NULL 
                    THEN
                    --(select id, customer, code, mean_v from public.data) as t
                    
                    --wish use this to set for load_date per row:
                    --select AVG(t.mean_v) from t, public.table 
                    --where t.customer = table.customer
                    --and t.code = table.code
                    
             END```

Answer 1

Maybe something like

UPDATE t
set mean_v =
            CASE 
                WHEN load_date IS NOT NULL 
                    THEN 
                    DATEDIFF(day, CONVERT(date, load_date),CONVERT(date, req_date))
                WHEN load_date IS NULL 
                    THEN (
                        SELECT AVG(tt.mean_v)
                        FROM public.table tt
                        WHERE t.customer = tt.customer
                        and t.code = tt.code
                    )
             END
FROM public.table t