Can we use CASE statement with INSERT on a big query table?

Question

i am trying to append values corresponding to unique key values in main table from another sub table without a join.

Eg:

MAIN_TABLE:

COL1 | COL2 | COL3
aaa  | 1111 | a111 
bbb  | 2222 | b222 
ccc  | 3333 | c333 
ddd  | 4444 | d444 
abc  | 1233 | b222 

SUB_TABLE

COL1 | COL2
Cat  | a111
Dog  | b222
bird | c333
fish | d444

I need the RESULT as below:

COL1 | COL2 | COL3 | COL4
aaa  | 1111 | a111 | Cat 
bbb  | 2222 | b222 | Dog 
ccc  | 3333 | c333 | Bird
ddd  | 4444 | d444 | fish
abc  | 1233 | b222 | Dog

I was trying to achieve that by using CASE statement in an INSERT into Col 4 in BigQuery.

I would love to know if there are any other approached to this without a join but instead mapping the values as a new column to the table.

Thank you in advance for your time. Please help!

Answer 1

If col2 is unique in sub_table, you can use a subquery:

select m.*,
    (select s.col1 from sub_table s where s.col2 = m.col3) as col4
from main_table m

Or you can join:

select m.*, s.col1
from main_table m
inner join sub_table s on s.col2 = m.col3

It is unclear what you mean by insert in your question. If you want to update col4 in existing rows of main_table, you can do:

update main_table m
set col4 = (select s.col1 from sub_table s where s.col2 = m.col3)