Use replace to update column value from another column-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

This T-SQL (I don't have Postgres) and isn't elegant, but it works..

;with l as (
    -- Match the longest value first
    select c.id c_id, v.id v_id, ROW_NUMBER () over (partition by c.id order by len(v.name) desc) r
    from code c
    join value v on charindex (v.name, c.name) > 0)
, l1 as (
    -- Select the longest value first
    select c_id, v_id from l where r = 1
    )
update c set name = REPLACE(c.name, v.name, '')
from l1
join code c on c.id = l1.c_id
join value v on v.id = l1.v_id