UPDATE TABLE WITH AVERAGE

I have a problem with my query :

UPDATE [DIM_Category]
SET Category 3 = (SELECT AVG(Category 2)
FROM [DIM_Category] GROUP BY Category)

I want to do like in this exemple and I don’t know how !

thanks for help !

>Solution :

As much as I dislike the concept you are doing here the query itself needs to be correlated. One way you can do that easily is with a cte. This will work on the example you posted. I included my code to create a table so I had something work with. You would obviously not need that part. 😉

create table DIM_Category
(
    Category varchar(20)
    , Category2 int
    , Category3 int
);

insert DIM_Category
(
    Category
    , Category2
) values
    ('OCS', 10800)
    ,('OCS', 29600)
    ,('Netflix', 24000)
    ,('Netflix', 22000);

with CatAvg as
(
    select Category
        , CatAverage = AVG(Category2)
    from DIM_Category
    group by Category
)
update c
set Category3 = ca.CatAverage
from DIM_Category c
join CatAvg ca on ca.Category = c.Category;

select *
from DIM_Category;