How to replace multiple values in a single column with SQL?

Question

I have a column with complex user id. I want to replace the text within my select query.

This creates a new column as updated_by for every single value. I want them to be replaced in a single column. How can I achieve this?

select replace(updated_by, '5eaf5d368141560012161636', 'A'),
       replace(updated_by, '5e79d03e9abae00012ffdbb3', 'B'),
       replace(updated_by, '5e7b501e9abae00012ffdbd6', 'C'),
       replace(updated_by, '5e7b5b199abae00012ffdbde', 'D'),
       replace(updated_by, '5e7c817c9ca5540012ea6cba', 'E'),
       updated_by
from my_table
GROUP BY updated_by;

In the absence of any aggregating functions, a GROUP BY clause is never appropriate. For further help, see [Why should I provide an MCRE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query), and note that fom the point of view of a potential respondent, '5e79d' is no less intelligible than '5e79d03e9abae00012ffdbb3' — Strawberry, Jun 12 '20 at 09:58

Erwin Brandstetter · Answer 1 · 2020-06-12T19:23:14.957

In Postgres I would use a VALUES expression to form a derived table:

To just select:

SELECT *
FROM   my_table m
JOIN  (
   VALUES
     ('5eaf5d368141560012161636', 'A')
   , ('5e79d03e9abae00012ffdbb3', 'B')
   , ('5e7b501e9abae00012ffdbd6', 'C')
   , ('5e7b5b199abae00012ffdbde', 'D')
   , ('5e7c817c9ca5540012ea6cba', 'E')
   ) u(updated_by, new_value) USING (updated_by);

Or LEFT JOIN to include rows without replacement.

You may need explicit type casts with non-default data types. See:

Casting NULL type when updating multiple rows

For repeated use, create a persisted translation table.

CREATE TABLE updated_by_translation (updated_by text PRIMARY KEY, new_value text);

INSERT INTO my_table
VALUES
  ('5eaf5d368141560012161636', 'A')
, ('5e79d03e9abae00012ffdbb3', 'B')
, ('5e7b501e9abae00012ffdbd6', 'C')
, ('5e7b5b199abae00012ffdbde', 'D')
, ('5e7c817c9ca5540012ea6cba', 'E')
;

Data types and constraints according to your actual use case.

SELECT *
FROM   my_table m
LEFT   JOIN updated_by_translation u USING (updated_by);

MySQL recently added a VALUES statement, too. The manual:

VALUES is a DML statement introduced in MySQL 8.0.19

But it requires the keyword ROW for every row. So:

...
   VALUES
     ROW('5eaf5d368141560012161636', 'A')
   , ROW('5e79d03e9abae00012ffdbb3', 'B')
   , ROW('5e7b501e9abae00012ffdbd6', 'C')
   , ROW('5e7b5b199abae00012ffdbde', 'D')
   , ROW('5e7c817c9ca5540012ea6cba', 'E')
...

@ErwinBrandsetter . . . Will this replace multiple values in one row? — Gordon Linoff, Jun 12 '20 at 11:00
@SNaRe: For repeated use, consider a translation table. I added a bit. — Erwin Brandstetter, Jun 12 '20 at 13:31

nbk · Answer 2 · 2020-06-12T10:35:17.000

This has to be nested liek this

SELECT 
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(updated_by,
                        '5e7c817c9ca5540012ea6cba',
                        'E'),
                    '5e7b5b199abae00012ffdbde',
                    'D'),
                '5e7b501e9abae00012ffdbd6',
                'C'),
            '5e79d03e9abae00012ffdbb3',
            'B'),
        '5eaf5d368141560012161636',
        'A'),
    updated_by
FROM
    my_table
GROUP BY updated_by

This will replace all occurring, patterns, if they are not foung nothing happens

klin · Accepted Answer · 2020-06-12T14:24:47.703

0

Use case:

select case updated_by
    when '5eaf5d368141560012161636' then 'A'
    when '5e79d03e9abae00012ffdbb3' then 'B'
    when '5e7b501e9abae00012ffdbd6' then 'C'
    when '5e7b5b199abae00012ffdbde' then 'D'
    when '5e7c817c9ca5540012ea6cba' then 'E'
    end as updated_by
from my_table

edited Jun 12 '20 at 14:24

answered Jun 12 '20 at 10:10

klin

112,967
15
204
232

score 0 · Answer 4 · answered Jun 12 '20 at 11:07

You can use a recursive CTE if you need to handle multiple values within a single row:

with replacements as (
      select '5eaf5d368141560012161636' as oldval, 'A' as newval union all
      select '5e79d03e9abae00012ffdbb3' as oldval, 'B' union all
      select '5e7b501e9abae00012ffdbd6' as oldval, 'C' union all
      select '5e7b5b199abae00012ffdbde' as oldval, 'D' union all
      select '5e7c817c9ca5540012ea6cba' as oldval, 'E'
     ),
     r as (
      select r.*, row_number() over (order by oldval) as seqnum
      from replacements r
     ),
     recursive cte (
      select r.seqnum, replace(t.updated_by, r.oldval, r.newval) as updated_by
      from my_table t join
           r
           on r.seqnum = 1
      union all
      select r.seqnum, replace(cte.updated_by, r.oldval, r.newval) as updated_by
      from cte t join
           r
           on r.seqnum = cte.seqnum + 1
     )
select cte.*
from cte
where seqnum = (select count(*) from replacements);

How to replace multiple values in a single column with SQL?

4 Answers4