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

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

Id	UserId
1	5
2	4

Test

Id	EmployeeId	UserId
1	1	5
2	2	4

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);

CodePudding user response：

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.

CodePudding user response：

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.