Vahn Vahn - 1 month ago 11
MySQL Question

Change a column name on select

I have a temporary table.

col_1 col_2 col_3 col_4
ABC B01 10 5
ABC B01 77 5
ABC B03 30 7
ABC B05 50 11
ABC B05 88 11
DEF B05 50 50
DEF B05 88 50
DEF B06 66 50
A01 B02 20 0
A01 B04 40 0
A02 B02 99 0


and this helper table:

ID1 ID2
ABC A01
DEF A02


What I'd like to do. If there are values in col_1 in temporary table that matches ID2 in helper table, the values are changed to ID1 in helper table. How to do this on a
select
?

So, when I do a select (
SELECT * FROM temporary
). This is the result:

col_1 col_2 col_3 col_4
ABC B01 10 5
ABC B01 77 5
ABC B03 30 7
ABC B05 50 11
ABC B05 88 11
DEF B05 50 50
DEF B05 88 50
DEF B06 66 50
ABC B02 20 0
ABC B04 40 0
DEF B02 99 0


Is it possible to do that? Thanks for your help

nb:

There are a lot of values in helper table. Not just two rows.

Answer

Left join with case

select   case
                when h.id2 = tt.col_1 then h.id1
                else tt.col_1
            end as col_1,
            col_2,col_3,col_4
from    temporary_table tt
left join helper h on tt.col_1 = h.id2
Comments