update record value if contains substring from other column

I have two tables, one containing all unique towns names in the UK and another one containing the human-inputted country of residence. Of course humans wrote stuff like “london, uk” or “cardiff, united kingdom”.

I need to consolidate the country_of_residence column so I have created a table with all possible towns, counties, cities and villages in the UK with almost 9000 records.

I want to update the column country_of_residence if its records contain any of the record in the column entity in the table uk_geo_entities.

I have read a lot answers here but they all try to update by wanting to check against or update one string, I need to do this against every record of a column.

a_table                          uk_geo_entities

country_of_residence | ...       entity | ...        
-------------------- + ...       ------ + ...
london, uk                       london
cardiff, england                 cardiff


UPDATE a_table
SET country_of_residence = 'united kingdom'
FROM (SELECT entity
      FROM uk_geo_entities) b
WHERE country_of_residence LIKE '%'||b.entity||'%';

and

UPDATE psc_sharing_dodgy_officers
SET country_of_residence = 'united kingdom'
WHERE country_of_residence LIKE '%'||(SELECT entity FROM 
uk_geo_entities)||'%';

run, but nothing happens.

After a working query I’d need to see

a_table                         

country_of_residence | ...          
-------------------- + ...     
united kingdom                       
united kingdom

Answer

You can do it with EXISTS in the WHERE clause:

UPDATE a_table a
SET country_of_residence = 'united kingdom'
WHERE EXISTS (
  SELECT entity 
  FROM uk_geo_entities
  WHERE a.country_of_residence LIKE '%' || entity || '%'
)

See the demo.

Advertisement

Answer