Use replace to update column value from another column

Question

I have database that looks like this

CREATE TABLE code (
  id SERIAL, 
  name VARCHAR(255) NOT NULL 
);

INSERT INTO code (name) VALUES ('random_value1_random');
INSERT INTO code (name) VALUES ('random_value123_random');

CREATE TABLE value (
  id SERIAL, 
  name VARCHAR(255) NOT NULL 
);

INSERT INTO value (name) VALUES ('value1');
INSERT INTO value (name) VALUES ('value123');

UPDATE code SET name = REPLACE(name, SELECT name from value , '');

I want to update my table code to remove a portion of a code and that code is coming from another table. My goal is to update all values of code and remove the portion of the string that matches another value. My end goal is to make all code.name in the example look like: random_random removing the value from the value table.

When tried using to replace with a query I get an error:

[21000] ERROR: more than one row returned by a subquery used as an expression

What is a cleaner better way to do this?

Answer 1

You can use REGEXP_REPLACE to replace multiple substrings in a string. You can use STRING_AGG to get the search pattern from the single search values.

UPDATE code SET name = 
  REGEXP_REPLACE( name,
                  (SELECT '(' || STRING_AGG(name, '|') || ')' from value),
                  ''
                );

This will leave you with 'random___random', not 'random_random'. If you only want to look for substrings separated with the underline character, then use

UPDATE code SET name = 
  TRIM('_' FROM 
        REGEXP_REPLACE(name, 
                       (SELECT '(' || STRING_AGG('_?' || name || '_?', '|') || ')' from value),
                       '_'
                      )
      );

Demo: https://dbfiddle.uk/RrOel8Ns