CODEWITHSUNDEEP
sqldatabasepostgresqlpattern-matchingpostgresql-9.5
Tytire Recubans
Tytire Recubans

Reputation: 997

update column table with value of column from table b if it contains substring from another column from table b

There are 2 table like below:

Table a                        Table b

country_name                   id | string_bone   | country_name
------------------------       ---+---------------+---------------
usa                            1  | usa           | united states  
u s a                          2  | u s a         | united states
united states of america       3  | america       | united states
america                        4  | ...           | ...

I need to update table_a.country_name with table_b.country_name if table_b.string_bone is contained in table_a.country_name.

I tried this:

UPDATE table_a a
SET country_name = b.country_name
WHERE EXISTS (SELECT country_name
              FROM table_b
              WHERE a.country_name LIKE '%' || string_bone || '%') b;

I would like table_a to look like this after the update:

Table a                        

country_name                   
------------------------       
united states                            
united states                          
united states      
united states

Here the dbfiddle link.

Upvotes: 0

Views: 42

Answers (2)

Miguel Carreira
Miguel Carreira

Reputation: 526

Try the following:

UPDATE table_a a
    SET country_name = b.country_name
    from table_a t
    inner join table_b b
    on t.country_name LIKE '%' || b.string_bone || '%';

Upvotes: 1

Shawn.X
Shawn.X

Reputation: 1363

OK, it's easy to implement this requirement, like below:

update table_a a set country_name = b.country_name from table_b b where a.country_name ~ b.country_name;

The data example as below:

postgres=# select * from table_a;
       country_name       
--------------------------
 usa
 u s a
 united states of america
 america
(4 rows)

postgres=# select * from table_b;
 country_name 
--------------
 america
 usa
 u s a
(3 rows)

postgres=# update table_a a set country_name = b.country_name from table_b b where a.country_name ~ b.country_name;
UPDATE 4
postgres=# select * from table_a;
 country_name 
--------------
 usa
 u s a
 america
 america
(4 rows)

Upvotes: 1

Related Questions