Check against multiple duplicate records

Question

Before inserting data into table i need to check against duplicate records and report list of those records. Query should not insert anything while there is at least one duplicate.

Number of records to insert: ~1000

Rows in table: ~1.000.000

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:

What is the efficient way of doing this.

Thanks in advance.

P.S. consider using php and mysql

Actually nothing. The only thing that comes to mind is to check every single record against duplication. But it doesn't seem efficient. What would you do? — Turgut, Mar 26 '12 at 17:40
Is there something preventing you from using a unique key to reject duplicates? — gapple, Mar 26 '12 at 17:43
@Turgut: we aren't fond of people who try nothing before asking a question :) You should go and try *something*. Maybe you'll have a better question. Maybe you'll have no question at all. — Sergio Tulentsev, Mar 26 '12 at 17:45

score 1 · Accepted Answer · answered Mar 26 '12 at 17:59

Say duplicate means a username already exists. so you can try:

1] use a for loop to group all usernames that's in your insert list: pseudo code follows:

  $names="";
  for(each element){
      $names.=$val.", ";
  //remove last comma ","

Then use following sql statement to get all duplicate entries.

  select * from myTable where username in ($names);

having obtained a list of all your duplicates, then insert as

insert into myTable (username, address, otherstuff)
values("henry", "35 skid", "who knows")
ON DUPLICATE KEY UPDATE 
otherstuff = values(otherstuff);

score 0 · Answer 2 · edited May 23 '17 at 11:52

0

One way of doing it might be to insert everything anyway, then select duplicates in order to output them; and if any are found to rollback the entire operation. That would be quite expensive though.

edited May 23 '17 at 11:52

Community

1
1

answered Mar 26 '12 at 17:56

Andrew Leach

12,945
1
40
47

Check against multiple duplicate records

2 Answers2