I have a table named table1, with one column named col1, which takes value in range 1-9.

table1
col1
1
9     
7     
2     
4     
6     
1     
9     
3     
5     

Now I want to add another column which maps values in col1 to another value given in a map. 1 -> A, 2 -> B, 3 -> C, 4 -> D, 5 -> E, 6 -> F, 7 -> G, 8 -> H, 9 -> I

I want results to look like below.

col1  col2 
1     A
9     I
7     G
2     B
4     D
6     F
1     A
9     I
3     C
5     E

My approach is to create a new table with mapping and then do a inner join.

CREATE TABLE map (
    col1 int,
    col2 varchar
);

INSERT INTO map
    (col1, col2)
VALUES
    (1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E'),(6,'F'),(7,'G'),(8,'H'),(9,'I');

SELECT table1.col1, map.col2
FROM table1 INNER JOIN map ON table1.col1 = map.col1

Is this efficient approach, are there better methods than this?

CodePudding user response：

Use case statement:

Update t 
Update new_column = 
           Case 
              WHEN col1 = 1 THEN 'A'
              WHEN col1 = 2 THEN 'B'
              WHEN col1 = 3 THEN 'C'
              WHEN col1 = 4 THEN 'D'
              WHEN col1 = 5 THEN 'E'
              WHEN col1 = 6 THEN 'F'
              WHEN col1 = 7 THEN 'G'
              WHEN col1 = 8 THEN 'H'
              WHEN col1 = 9 THEN 'I'
           END
           FROM table1 t

CodePudding user response：

Although creating map table can be a good idea, assume you need to fill the map table with query. Then case statement is useful.

CASE col1
    WHEN 1 THEN 'A'
    WHEN 2 THEN 'B'
    WHEN 3 THEN 'C'
    WHEN 4 THEN 'D'
    -- And so on
END