Check duplicate entry to the inserted table while using a subquery in MySQL

I have two tables: Employee and Test. I need to insert data from Employee to Test table. I am doing this successfully.

But, the problem is that, I need to check duplicate entry in the Test Table. That means if there exists a row with the same EmployeeId and UserId, it should not be inserted the Test table from the employee. I am using MaridaDB. I used IGNORE keyword in my query, but unfortunately this is not checking the duplicate! I am stuck in this 🙁

Employee

Test

The query I am using:

INSERT IGNORE INTO authdb.test (EmployeeId, UserId)

(SELECT e.Id, e.UserId FROM basedb.employee e
WHERE e.UserId != 0);

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Because you can’t INSERT and SELECT to the same table:

CREATE TEMPORARY TABLE tmp
(EmployeeId INT NOT NULL,
UserId INT NOT NULL,
PRIMARY KEY ( EmployeeId, UserId))
AS SELECT e.EmployeeId, e.UserId
FROM EmployeeId e
LEFT JOIN Test
  USING (EmployeeId, UserID)
WHERE Test.EmployeeId IS NULL
   AND e.UserId != 0

Followed by:

INSERT INTO Test (EmployeeId, UserId)
SELECT * FROM tmp

This is subject to race conditions if Test items are removed between statements.

Method 2

INSERT IGNORE relies on a primary or unique key for determining the what is unique.

So either:

ALTER TABLE test ADD UNIQUE KEY eu_uniq (EmployeeId,UserId);

or:

ALTER TABLE test ADD PRIMARY KEY (EmployeeId,UserId);

This assumes Id isn’t a primary key already. If there’s no use of it, it can be removed.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0