Trying to insert values from one table to another where a surrogate key match is not found

Question

I am trying to simulate a full outer join, comparing two tables against each other. The batch_import table contains records that need to be inserted, only if they don't exist in the employees table. To do this I have tried the following query:

INSERT INTO employees (forename, surname, employersId, custom_corpore_headOffice, contractId)
SELECT firstname, surname, employeenumber, dob, store, contractId
FROM batch_import
LEFT JOIN employees ON batch_import.employeenumber = employees.employersId AND batch_import.contractId = employees.contractId
UNION ALL
SELECT forename, surname, employersId, custom_corpore_headOffice, contractId
FROM batch_import RIGHT JOIN employees ON batch_import.employeenumber = employees.employersId AND batch_import.contractId = employees.contractId
WHERE batch_import.employeenumber IS NULL AND batch_import.contractId IS NULL
                       ";

however when I run this query I get the message back from mysql: ERROR 1137 (HY000): Can't reopen table: 'batch_import'

I am guessing it is the logic of my UNION ALL being incorrect. Could someone please help me find a solution?

EDIT:: I have tried this query and it keeps adding records onto the table, starting at the last record. I have also tried:

                    INSERT INTO employees (forename, surname, employersId, dateOfBirth, custom_corpore_headOffice, contractId)
                SELECT firstname, batch_import.surname, employeenumber, dob, store, batch_import.contractId
                FROM batch_import
                LEFT JOIN employees ON batch_import.employeenumber = employees.employersId AND batch_import.contractId = employees.contractId
                UNION
                SELECT firstname, batch_import.surname, employeenumber, dob, store, batch_import.contractId
                FROM batch_import RIGHT JOIN employees ON batch_import.employeenumber = employees.employersId AND batch_import.contractId = employees.contractId;

but still to no avail. Instead of ignoring what exists it simply writes everything to the end of the table.

Also tried: INSERT INTO employees (forename, surname, employersId, dateOfBirth, custom_corpore_headOffice, contractId) SELECT firstname, batch_import.surname, employeenumber, dob, store, batch_import.contractId FROM batch_import LEFT JOIN employees ON employees.employersId = NULL;

I have found out that ERROR 1137 (HY000): Can't reopen table: 'batch_import could be thrown because batch_import is a temporary table — dickturnip, Jan 07 '15 at 10:54
Glad you found the issue. The logic looks correct; you can also use UNION and then you won't need the where clause as UNION will return only distinct values. — Jayvee, Jan 07 '15 at 11:54
Hmm it seems to be adding the data to the end of the existing data in the database. — dickturnip, Jan 07 '15 at 13:44
Ok, so you are not really looking for a full join. You just need the left join where employees.employersId is null. — Jayvee, Jan 07 '15 at 14:26
Really appreciate the help Jayvee, I have amended the query like so: INSERT INTO employees (forename, surname, employersId, dateOfBirth, custom_corpore_headOffice, contractId) SELECT firstname, batch_import.surname, employeenumber, dob, store, batch_import.contractId FROM batch_import LEFT JOIN employees ON employees.employersId = NULL; but i am getting the same result as before, i am totally baffled. the data in batch_import is exactly the same as in the employes table so it should not write anything? — dickturnip, Jan 07 '15 at 14:31

score 1 · Accepted Answer · answered Jan 07 '15 at 14:35

OPTION 1

INSERT INTO employees (forename, surname, employersId, custom_corpore_headOffice, contractId)
SELECT firstname, surname, employeenumber, dob, store, contractId
FROM batch_import
WHERE NOT EXISTS (Select 1 From employees where batch_import.employeenumber = employees.employersId AND batch_import.contractId = employees.contractId)

OPTION 2

INSERT INTO employees (forename, surname, employersId, custom_corpore_headOffice, contractId)
SELECT firstname, surname, employeenumber, dob, store, contractId
FROM batch_import
LEFT JOIN employees ON batch_import.employeenumber = employees.employersId AND batch_import.contractId = employees.contractId
WHERE employees.employersId is NULL

You are officially my hero. I am not worthy – dickturnip Jan 07 '15 at 14:38 — dickturnip, Jan 07 '15 at 14:38

Trying to insert values from one table to another where a surrogate key match is not found

1 Answers1