I have a table Locality-market with column as :
Locality_id
Locality_name
market_id
market_name
i am trying to update locality-name to market_name the query i am using is:
update table_1
set locality_name = market_name
where locality_name = ''
But the problem is i am getting different locality_name for a similar locality_id
I want to have one locality_name for one locality_id
1414, Nord Ouest, 110, Port-de-Paix 1415, Ouest , 109, Port-au-Prince 1416, Sud, 102, Cayes 1417, Sud Est, 105, Jacmel 1425, *blank* , 2601, San Pedro Sula 1426, *blank*, 2608, Tegucigalpa 1484, *blank*, 4676, Mayabunder 1484, *blank*, 1392, Port Blair 1485, *blank*, 4666, Jadcherla 1485, *blank*, 4671, Karimnagar 1485, *blank*, 4692, Suryapet 1485, *blank*, 4695, Tirupathi 1485, *blank*, 4699, Visakhapatnam
i want to update locality name in such a way that i always get only one locality_name for every locality_id
1414, Nord Ouest, 110, Port-de-Paix 1415, Ouest, 109, Port-au-Prince 1416, Sud, 102, Cayes 1417, Sud Est, 105, Jacmel 1425, San Pedro Sula, 2601, San Pedro Sula 1426, Tegucigalpa, 2608, Tegucigalpa 1484, Mayabunder, 4676, Mayabunder 1484, Mayabunder, 1392, Port Blair 1485, Jadcherla, 4666, Jadcherla 1485, Jadcherla, 4671, Karimnagar 1485, Jadcherla, 4692, Suryapet 1485, Jadcherla, 4695, Tirupathi 1485, Jadcherla, 4699, Visakhapatnam
CodePudding user response：
You can use a window function like FIRST_VALUE in a CTE.
Then update the CTE to update the table.
;with cte as (
select locality_id, locality_name, market_id, market_name
, new_locality_name = first_value(market_name)
over (partition by locality_id
order by market_name asc)
from table_1
where (locality_name is null or locality_name = '')
and (market_name is not null)
)
update cte
set locality_name = new_locality_name;