CODEWITHSUNDEEP
sqlhadoophiveimpala
DanteeChaos
DanteeChaos

Reputation: 333

Use the result of a select query in an insert statement in IMPALA script

The result of the select statement below changes on a daily basis but it will always be 1 row:

select col1 from table_x;

I want to use that result in the following insert statement:

insert overwrite table table_y
  select
  a.col1,
  b.col2,
  <result_of_select_statament_here>,
  a.col4
from table_y a
inner join table_z b
on a.col3 = b.col3;

Is there a way to put the result of that select statement into a variable and then use it in the insert statement?

If not, is there any other way?

Upvotes: 1

Views: 3206

Answers (1)

Koushik Roy
Koushik Roy

Reputation: 7387

You can use below workaround. Since you don't want to add new join conditions, you can do this in 2 steps. step 1 - combine table_x c and table_y on c.col1 = a.col1 and create small table table_tmp. step 2 - join table_tmp with table_z

create table table_tmp as 
  select
  a.col1, 
  c.col1,
  a.col4
from table_y a 
left join table_x c on c.col1 = a.col1;

insert overwrite table table_y
  select
  a.col1,
  b.col2,
  a.col1,
  a.col4
from table_tmp a
inner join table_z b on a.col3 = b.col3
;

Upvotes: 1

Related Questions