Update using median value of another column

1

I need to update median value of daily_vaccination of countries whose daily_vaccinations is NULL.

update country_vaccination_stats
set daily_vaccinations = ( select median(t2.daily_vaccinations) from country_vaccination_stats t2 GROUP by t2.country)
where daily_vaccinations ISNULL

Please help

2

Assuming you are using Microsoft SQL Server, which does not have a built-in Median function, you can do something like shown below. Since you haven't provided any sample data, or even which table you are trying to update, this is just a guess on my part. If that is not what you are expecting, post more details - sample data, table schema etc.

;WITH MedianCTE AS
(
    SELECT 
        country,
        MedianVaccinations = PERCENTILE_CONT(.5) WITHIN GROUP(ORDER BY daily_vaccinations) OVER (PARTITION BY country)
    FROM
        country_vaccination_stats
)
UPDATE t
    SET daily_vaccinations = MedianVaccinations
FROM
    country_vaccination_stats t
    INNER JOIN MedianCTE AS c ON
        c.country = t.country
WHERE
    t.daily_vaccinations IS NULL;
3

Hi
Thanks for the reply.
Here is the sample table.

image
image760×352 9.91 KB

I need to update null value of daily_vaccinations for any country with its median value.
Please let me know

4

Hi,
I tried the query in sql server and it works. Thanks for the help.