Inner join to multiple columns and use in INSERT INSTO....SELECT

Question

Table CUST_DAY0_MERGE has the following columns

NEW_CUST_ID
ACTIVE_REF_ID
OLD_REF_ID_1
OLD_REF_ID_2
OLD_REF_ID_3

I would like to create an insert statement into table CUST_REVIEWS, with the following columns:

CUST_REVIEW_ID
RUN_DATE
CUST_ID
CUST_SOURCE_UNIQUE_ID
CUST_SOURCE_REF_ID

This insert statement need to inner join query withCUST_DAY0_MERGE with the following conditions:

CUST_DAY0_MERGE.OLD_REF_ID_1`=`CUST_REVIEWS.CUST_SOURCE_REF_ID
CUST_DAY0_MERGE.OLD_REF_ID_2`=`CUST_REVIEWS.CUST_SOURCE_REF_ID
CUST_DAY0_MERGE.OLD_REF_ID_3`=`CUST_REVIEWS.CUST_SOURCE_REF_ID

If above conditions fullfied, a new record will be inserted to CUST_REVIEWS with the following conditions:

CUST_REVIEWS.CUST_ID`=`CUST_DAY0_MERGE.NEW_CUST_ID
CUST_REVIEWS.CUST_SOURCE_UNIQUE_ID`=`CUST_DAY0_MERGE.NEW_CUST_ID

For others columns should remain unchanged as per existing value.

I have a sample statement as per following:

INSERT INTO CUST_REVIEWS(CUST_REVIEW_ID,RUN_DATE,CUST_ID,CUST_SOURCE_UNIQUE_ID,CUST_SOURCE_REF_ID)
SELECT CUST_REVIEW_ID,RUN_DATE,CUST_ID,CUST_SOURCE_UNIQUE_ID,CUST_SOURCE_REF_ID
FROM CUST_REVIEWS reviews
INNER JOIN CUST_DAY0_MERGE merge1
ON (reviews.CUST_SOURCE_REF_ID = merge1.OLD_REF_ID_1)
INNER JOIN CUST_DAY0_MERGE merge2
ON (reviews.CUST_SOURCE_REF_ID = merge2.OLD_REF_ID_2)
INNER JOIN CUST_DAY0_MERGE merge3
ON (reviews.CUST_SOURCE_REF_ID = merge3.OLD_REF_ID_3);

How can enhance the insert statement by changing CUST_ID and CUST_SOURCE_UNIQUE_ID with the condition I would like to achieve?

Answer 1

"If above conditions fullfied, a new record will be inserted to CUST_REVIEWS" - you mean

"If at least one of above" or
"If all of the above" ?

In second case you may use a single join:

INNER JOIN CUST_DAY0_MERGE merge
ON (reviews.CUST_SOURCE_REF_ID = merge.OLD_REF_ID_1)
AND
(reviews.CUST_SOURCE_REF_ID = merge.OLD_REF_ID_2)
AND
(reviews.CUST_SOURCE_REF_ID = merge.OLD_REF_ID_3)

In first case you may use "OR" but using "UNION" instead is reported as being more optimal:

INNER JOIN CUST_DAY0_MERGE merge
    ON (reviews.CUST_SOURCE_REF_ID = merge.OLD_REF_ID_1)
    OR
    (reviews.CUST_SOURCE_REF_ID = merge.OLD_REF_ID_2)
    OR
    (reviews.CUST_SOURCE_REF_ID = merge.OLD_REF_ID_3)

vs.

       INSERT INTO CUST_REVIEWS(CUST_REVIEW_ID,RUN_DATE,CUST_ID,CUST_SOURCE_UNIQUE_ID,CUST_SOURCE_REF_ID)  

       SELECT CUST_REVIEW_ID,RUN_DATE,merge.NEW_CUST_ID as CUST_ID,merge.NEW_CUST_ID as CUST_SOURCE_UNIQUE_ID,CUST_SOURCE_REF_ID
FROM CUST_REVIEWS reviews
       INNER JOIN CUST_DAY0_MERGE merge
           ON (reviews.CUST_SOURCE_REF_ID = merge.OLD_REF_ID_1)

       UNION
       SELECT CUST_REVIEW_ID,RUN_DATE,merge.NEW_CUST_ID as CUST_ID,merge.NEW_CUST_ID as CUST_SOURCE_UNIQUE_ID,CUST_SOURCE_REF_ID
FROM CUST_REVIEWS reviews    
       INNER JOIN CUST_DAY0_MERGE merge
          ON (reviews.CUST_SOURCE_REF_ID = merge.OLD_REF_ID_2)

       UNION
       SELECT CUST_REVIEW_ID,RUN_DATE,merge.NEW_CUST_ID as CUST_ID,merge.NEW_CUST_ID as CUST_SOURCE_UNIQUE_ID,CUST_SOURCE_REF_ID
FROM CUST_REVIEWS reviews    
       INNER JOIN CUST_DAY0_MERGE merge
          ON (reviews.CUST_SOURCE_REF_ID = merge.OLD_REF_ID_3)