Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

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

1 Answer 1

up vote 1 down vote accepted

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 !!!

share|improve this answer
    
It makes sense. Thanks for taking time. –  Turgut Mar 27 '12 at 6:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.