0

I have two tables.

table1 (TABLE_HRD_CONTACTS_V01) Col_Primary_Key,Col_Call,COL_EMAIL

table2 (TABLE_EMAIL) Col_Primary_Key,Col_Call,COL_EMAIL

Both have more.

The application adds to Table1. My code is trying to insert in table2 exists in table1

This is code run to build the first time how is best way to insert going froward?

INSERT INTO TABLE_EMAIL (`COL_PRIMARY_KEY`, `COL_CALL`, `COL_EMAIL`) 
SELECT `COL_PRIMARY_KEY`, `COL_CALL`, `COL_EMAIL` 
FROM TABLE_HRD_CONTACTS_V01 
WHERE `COL_PRIMARY_KEY` <> 999999999 ;
3

1 Answer 1

0

One way to check for existence in another table is with an outer join (such as a LEFT JOIN) filtered down to only the rows that didn't match, like so:

INSERT INTO TABLE_EMAIL (`COL_PRIMARY_KEY`, `COL_CALL`, `COL_EMAIL`) 
SELECT T1.`COL_PRIMARY_KEY`, T1.`COL_CALL`, T1.`COL_EMAIL` 
FROM TABLE_HRD_CONTACTS_V01 AS T1
LEFT JOIN TABLE_EMAIL AS T2
    ON T1.COL_PRIMARY_KEY = T2.COL_PRIMARY_KEY
WHERE T1.`COL_PRIMARY_KEY` <> 999999999
    AND T2.COL_PRIMARY_KEY IS NULL; -- Didn't match a row in T2 so doesn't exist in T2 yet
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.