How to search and replace in a table?-CodePudding

I have to search in a table and update them by the new one:

the table contains data about locations:

Table1

name  | location
---------------
rolf  | India Mumbai
Jorgee| India Agra
ahmed | Iraq
jose  | France Paris
Morata| Italia Milan

i need to update it to get the next result:

name  | location
---------------
rolf  | Asia
Jorgee| Asia
ahmed | Asia
jose  | Europe
Morata| Europe

I tried with this query but i didn't know how to create it correctly with update set:

Case
When REGEXP_MATCHES(location, ".*India.*|.*Japan.*|.*UAE.*|.*Qatar.*|.*Syrie.*|.*Iraw.*") then "Asia" 
When REGEXP_MATCHES(location, ".*Colombia.*|.*Argentina.*|.*Peru.*|.*Brazil.*") then "Lat-America" 
When REGEXP_MATCHES(location, ".*France.*|.*Germany.*|.*Spain.*") then "Europe"

as am new to SQL i don't know how to do it ?

have anyone idea how to update a column based on many conditions ?

CodePudding user response：

You need to add the 'UPDATE' and FROM keywords to correctly form the syntax. It would be like this.

UPDATE TableName
      SET "location" = CASE
    When REGEXP_MATCHES(location, ".*India.*|.*Japan.*|.*UAE.*|.*Qatar.*|.*Syrie.*|.*Iraw.*") then "Asia" 
    When REGEXP_MATCHES(Location, ".*Colombia.*|.*Argentina.*|.*Peru.*|.*Brazil.*") then "Lat-America" 
    When REGEXP_MATCHES(Location, ".*France.*|.*Germany.*|.*Spain.*") then "Europe" 
    ELSE '' //WHAT YOU NEED
END
FROM TableName

CodePudding user response：

you can use following code :

UPDATE <table> SET location = REGEXP_REPLACE(location, '.*India', 'Asia', 'i');

Refer link below replace functions for PostgreSQL :

https://www.postgresqltutorial.com/postgresql-string-functions/postgresql-replace/