CODEWITHSUNDEEP
arraysstringpostgresqlselect
Gulya
Gulya

Reputation: 131

Select x value within a text - string_to_array

I have a column in table1 that contains names separated with commas, like a,b,c

names result
a,d,e
a,c,e,f
c,d,f,g

Another column with a single name in table2, like a or b or c

line name origin
1 a US
2 b UK
3 c UK
4 d AUS
5 e CAN
6 f UK
7 g UK

And I want to update table1.result if any names from table1.names are in table2.name & origin = UK.

Tried this, but getting error;

update table1 as t1
set result = 
(select name from table2 where origin='UK') = any(string_to_array(t1.names, ','))

Upvotes: 1

Views: 52

Answers (1)

klin
klin

Reputation: 121604

Use exists(...) if the result you want is boolean:

update table1 as t1
set result = exists(
    select name 
    from table2 
    where origin = 'UK'
    and name = any(string_to_array(t1.names, ','))
    );

Test it in db<>fiddle.

If you want to get the names, use string_agg():

update table1 as t1
set result = (
    select string_agg(name, ',') 
    from table2 
    where origin = 'UK'
    and name = any(string_to_array(t1.names, ','))
    );

Db<>fiddle.

Upvotes: 1

Related Questions