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 🙁
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);
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.
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
INSERT INTO Test (EmployeeId, UserId) SELECT * FROM tmp
This is subject to race conditions if
Test items are removed between statements.
INSERT IGNORE relies on a primary or unique key for determining the what is unique.
ALTER TABLE test ADD UNIQUE KEY eu_uniq (EmployeeId,UserId);
ALTER TABLE test ADD PRIMARY KEY (EmployeeId,UserId);
Id isn’t a primary key already. If there’s no use of it, it can be removed.