1

I have a table with column 'columnx' with values (1,2,1,NULL,1,1,3)

Column X Column Y
1 3
2 3
1 1
Null 1
1 1
1 1

and I am hoping to replace these num with actual data from another table 'X1' as follows:

Enum Val Description
1 red
2 blue
3 green
4 yellow

so the output should like this:

Column X Column Y
red 3
blue 3
red 1
Null 1
red 1
red 1

I need to do the same for column Y as well for which the data is sourced from a different table and has different values for the corresponding enum

all the tables are in the same DB, I was hoping to use REPLACE but unable to follow through

what would be the best way to do it if the columns are static?

Appreciate any help

1 Answer 1

0

If by REPLEACE you mean update the values on the table a simple inner join update clause would do the trick.

MySQL example

update my_table m
inner join x1 on x1.EnumVal=m.ColumnX
set m.ColumnX=x1.Description

SQL Server example

update  m
set m.ColumnX=x1.Description
from my_table m
inner join x1 on x1.EnumVal=m.ColumnX

Given data example

create table my_table (
ColumnX varchar(15) ,
ColumnY varchar(15) );

insert into my_table values (1,3), (2,3),(1,1),(Null,1),(1,1),(1,1);

create table x1 (
EnumVal int ,
Description varchar(15));

insert into x1 values (1,'red'),(2,'blue'),(3,'green'),(4,'yellow');

Result

ColumnX   ColumnY
  red     3
  blue    3
  red     1
  null    1
  red     1
  red     1
1
  • Thank you for your answer :)
    – CodeNewbie
    May 9, 2023 at 19:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.