CODEWITHSUNDEEP
sqlpostgresql
Luffydude
Luffydude

Reputation: 802

Postgresql how to update a column if a string has a word in it?

I have 2 tables:

table A that has a postcode column with one postcode each row (example row: E1 8NF)

table B that has a postcode column with multiple postcodes separated by a comma (example row: E1 8NF, E1 8NG, E1 8NJ)

If the postcode from table A exists in table B, I want to give it a 1

How to do this in postgresql? My query so far

UPDATE tablea
set pcd = 1 where tablea.postcode exists in tableb.postcode ??

Upvotes: 0

Views: 1094

Answers (3)

user330315
user330315

Reputation:

You can convert the comma separated list into an array, then use that in a sub-select:

update tablea a
    set pcd = 1
where exists (select *
              from tableb b
              where a.postcode = any(string_to_array(b.postcodes, ','))
              );

If the values are stored with spaces between the commas, you would need to apply a trim() which probably is easier with a join in the update:

update tablea a
    set pcd = 1
from (
   select trim(x.pc) as pc
   from tableb b, 
        unnest(string_to_array(b.postcodes)) as x(px)
) t
where a.postcode = t.pc;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Storing lists in a comma separated field is a really bad idea. It is even worse in a database such as Postgres that has very reasonable alternatives -- such as arrays and JSON fields. But, sometimes we are stuck with other people's really bad decisions.

One method is:

update tablea a
    set pcd = 1
    where exists (select 1
                  from tableb b
                  where ',' || a.postcode || ',' like ',' || replace(b.postcodes, ', ', ',') || ','
                 );

Upvotes: 2

Piotr Rogowski
Piotr Rogowski

Reputation: 3890

try this way:

UPDATE tablea set pcd = 1 where postcode in (select b.postcode from tableb);

Upvotes: 0

Related Questions