Error while inserting values in hive table

Question

I need to insert values from 2 tables in one table. In this table I need to assign IDs for both primary and other drugs which are in one column in another table. So all columns will come from one table (d_temp_drugs) and only IDs of primary and other drugs will come from other table (unique_drugs_drug_id)

I am using join for this and getting error "Error while compiling statement: FAILED: SemanticException [Error 10007]: Ambiguous column reference drug_id in _u1-subquery1"

Below is the code I am using to create table and insert values in it. Pls suggest a fix.

create table study_drug_mapping
(
    trial_identifier string,
    cto_id int,
    primary_drug string,
    primary_drug_id int,
    other_drug string,
    other_drug_id int
)

insert into table study_drug_mapping (trial_identifier, cto_id, primary_drug, primary_drug_id, other_drug, other_drug_id)
select d_temp_drugs.trial_identifier, d_temp_drugs.cto_id, d_temp_drugs.primary_drugs_sorted, unique_drugs_drug_id.drug_id, d_temp_drugs.other_drugs_sorted, unique_drugs_drug_id.drug_id
from
(
select d_temp_drugs.trial_identifier, d_temp_drugs.cto_id, d_temp_drugs.primary_drugs_sorted, drugs.drug_id, d_temp_drugs.other_drugs_sorted, drugs.drug_id
from d_temp_drugs left join unique_drugs_drug_id drugs on d_temp_drugs.primary_drugs_sorted = drugs.unique_drug_name
union
select d_temp_drugs.trial_identifier, d_temp_drugs.cto_id, d_temp_drugs.primary_drugs_sorted, drugs.drug_id, d_temp_drugs.other_drugs_sorted, drugs.drug_id
from d_temp_drugs left join unique_drugs_drug_id drugs on d_temp_drugs.other_drugs_sorted = unique_drugs_drug_id.unique_drug_name
) a

Answer 1

Please give this a try, you are pretty close you are referencing the SUBQUERY table in the final SELECT which throws the error

INSERT INTO TABLE study_drug_mapping (trial_identifier, cto_id, primary_drug, primary_drug_id, other_drug, other_drug_id)
SELECT 
        A.trial_identifier, 
        A.cto_id, 
        A.primary_drugs_sorted, 
        A.drug_id, 
        A.other_drugs_sorted, 
        A.drug_id
FROM
(
SELECT 
        d_temp_drugs.trial_identifier, 
        d_temp_drugs.cto_id, 
        d_temp_drugs.primary_drugs_sorted, 
        drugs.drug_id, 
        d_temp_drugs.other_drugs_sorted, 
        drugs.drug_id
FROM d_temp_drugs 
LEFT JOIN unique_drugs_drug_id drugs ON d_temp_drugs.primary_drugs_sorted = drugs.unique_drug_name
UNION --Make use of UNION ALL incase you need all the data from both the SELECT statements 
SELECT 
        d_temp_drugs.trial_identifier, 
        d_temp_drugs.cto_id, 
        d_temp_drugs.primary_drugs_sorted, 
        drugs.drug_id, 
        d_temp_drugs.other_drugs_sorted, 
        drugs.drug_id
FROM d_temp_drugs 
LEFT JOIN unique_drugs_drug_id drugs ON d_temp_drugs.other_drugs_sorted = unique_drugs_drug_id.unique_drug_name
) A

Answer 2

try this:

create table study_drug_mapping
(
    trial_identifier string,
    cto_id int,
    primary_drug string,
    primary_drug_id int,
    other_drug string,
    other_drug_id int
); 

insert into table study_drug_mapping 
select 
    trial_identifier, 
    cto_id, 
    primary_drugs_sorted, 
    drug_id, 
    other_drugs_sorted, 
    drug_id
from
(
select 
    d_temp_drugs.trial_identifier as trial_identifier, 
    d_temp_drugs.cto_id as cto_id, 
    d_temp_drugs.primary_drugs_sorted as primary_drugs_sorted, 
    drugs.drug_id as drug_id, 
    d_temp_drugs.other_drugs_sorted as other_drugs_sorted, 
    drugs.drug_id as drug_id
from d_temp_drugs left join unique_drugs_drug_id drugs on (d_temp_drugs.primary_drugs_sorted = drugs.unique_drug_name)
union 
select
    d_temp_drugs.trial_identifier as trial_identifier, 
    d_temp_drugs.cto_id as cto_id, 
    d_temp_drugs.primary_drugs_sorted as primary_drugs_sorted, 
    drugs.drug_id as drug_id, 
    d_temp_drugs.other_drugs_sorted as other_drugs_sorted, 
    drugs.drug_id as drug_id
from d_temp_drugs left join unique_drugs_drug_id drugs on (d_temp_drugs.other_drugs_sorted = unique_drugs_drug_id.unique_drug_name)
)a