automate process to avoid manual update of a column in sql

i have a table like:

in which i'm trying to change the value of a column city like: where city like '%Athen%' to be updated as 'Athens' and city like '%Berlin%' updated to 'Berlin'

i tried to find a solution using replace, ex:

select replace(city, '%Athen%', 'Athens') 
from person
where city like '%Athen%'

but it doesn't work.

the result of the table should be that:

Solution 1:

Are you possibly after a searched case expression?

select case
    when city like '%Athen%' then 'Athens'
    when city like '%Berlin%' then 'Berlin'
else city end as City
from person

Edit - not clear if you are actually trying to update rows but if so you can update all rows at once with as many expressions as required:

update person set city=
    case
      when city like '%Athen%' then 'Athens'
      when city like '%Berlin%' then 'Berlin'
      ...
    else city end

If you only have the two criteria you can add

where city like '%Athen%' or city like '%Berlin%'

Solution 2:

One way is to use update statment UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

update person
SET city = 'Athens'
where city like '%Athen%'