2

I have data that i should insert into table. But before inserting i need to check against duplicate records and report list of those records.

Table:

CREATE TABLE `test` (
  `A` varchar(19) NOT NULL,
  `B` varchar(9) NOT NULL,
  KEY `A` (`A`),
  KEY `B` (`B`)
) ENGINE=InnoDB;

I need to check for both columns:

Number of records to insert: ~1000

Rows in table: ~1.000.000

What is the efficient way of doing this.

Thanks in advance.

3
  • Are the duplicates you checking for against column A, column B, or both at the same time? Mar 26, 2012 at 18:44
  • Need to determine whether at least one of columns is duplicate
    – Turgut
    Mar 26, 2012 at 18:59
  • Right now, the table design you are showing would allow duplicate. I will update my answer to reflect this. Mar 26, 2012 at 19:01

1 Answer 1

1

This would depend on the table's layout.

Suppose you have the following table

CREATE TABLE `mydata` ( 
    `A` varchar(19) NOT NULL, 
    `B` varchar(9) NOT NULL, 
    KEY `A` (`A`), 
    KEY `B` (`B`) 
) ENGINE=InnoDB; 

Before you insert 1000 rows into mydata, you could do preload them into another table called mynewdata like this:

CREATE TABLE mynewdata LIKE mydata;
CREATE TABLE mynewdups LIKE mydata;
INSERT INTO mynewdata ... ;
INSERT INTO mynewdups SELECT * FROM mynewdata;

Next delete all rows in mynewdata that matches A or B in mydata

DELETE T1.* FROM mynewdata T1 INNER JOIN mydata T2 ON T1.A=T2.A OR T1.B=T2.B;

What's left in mydata are rows that do not have A or B matching

What about the rows that matched? Run this

DELETE T1.* FROM mynewdups T1 LEFT JOIN mydata T2
ON T1.A=T2.A OR T1.B=T2.B
WHERE T2.A IS NOT NULL;

What's left in mynewdata is data to import

What's left in mynewdups is data that had a dup key in mydata

Give it a Try !!!

0

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.