| order_id | Products | Country |
|---|---|---|
| MB-123 | Bread | US |
| MB-123 | Milk | |
| MB-1256 | Cheese | UK |
| MB-1256 | Tomato Sauce | |
| MB-1256 | Milk |
The missing values in the above table needs to be filled with the same country names for the same order_id.
I tried with coalesce with window function but I am not able to fill the null value for each orderid. I want null values in country column to be filled by the country name for the same order_id.
I want the desired output as the table shown below :-
| order_id | Products | Country |
|---|---|---|
| MB-123 | Bread | US |
| MB-123 | Milk | US |
| MB-1256 | Cheese | UK |
| MB-1256 | Tomato Sauce | UK |
| MB-1256 | Milk | UK |
CodePudding user response：
In PostgreSQL, you can use the MAX window function, that allows you to get the non-null value for each "order_id" partition.
SELECT order_id, Products, MAX(Country) OVER(PARTITION BY order_id) AS Country
FROM tab
If you need to update the existing table, you can compute the maximum value for each country, then apply a JOIN operation inside the UPDATE` statement:
WITH cte AS (
SELECT order_id, Products, MAX(Country) OVER(PARTITION BY order_id) AS Country
FROM tab
)
UPDATE tab
SET Country = cte.Country
FROM cte
WHERE cte.order_id = tab.order_id AND cte.Products = tab.products
AND tab.Country IS NULL
Check the demo here.