SQL: set field values based on single subquery-CodePudding

I have a table of values. let's say:

create table table1 (FirstName varchar(255), LastName varchar(255));

INSERT INTO table1 (FirstName, LastName) VALUES ('Mariah1', 'Billy3');
INSERT INTO table1 (FirstName, LastName) VALUES ('Mo2', 'Molly2');
INSERT INTO table1 (FirstName, LastName) VALUES ('Sally3', 'Silly1');

I want to update all values to drop the numbers in names. so I tried:

UPDATE table1 t
SET (FirstName, LastName) = (
  select
  regexp_matches(FirstName ,'(\w )\d ') as updatedFirstName,
  regexp_matches(LastName ,'(\w )\d ') as updatedLastName
  FROM table1 u
  WHERE u.FirstName = t.FirstName and u.LastName = t.LastName
)

but then I get for instance:

{Mariah}, {Billy}

as values instead of:

Mariah, Billy

I tried adding [0] at the end, added extra () etc with no luck.

I'd like to know(with psql in mind ideally):

If I can fix the above query by converting a subquery returning a single row, into a simple tuple. ie ('abc', 'def')
have an alternative way of doing the above update

CodePudding user response：

I would use substring or regexp_replace:

substring extracts everything that is not a number:

update table1
   set firstname = substring(firstname from '[^0-9] '),  
       lastname = substring(lastname from '[^0-9] ');
where firstname ~ '[0-9]'
   or lastname ~ '[0-9]';

or use regexp_replace to remove the numbers:

update table1
   set firstname = regexp_replace(firstname, '[0-9] ', '', 'g'),
       lastname = regexp_replace(lastname, '[0-9] ', '', 'g')
where firstname ~ '[0-9]'
   or lastname ~ '[0-9]';

The WHERE clause is used to avoid updating rows that don't need updating