CODEWITHSUNDEEP
phpmysqlsqlinsert
Ogugua Belonwu
Ogugua Belonwu

Reputation: 2141

Using MySQL IGNORE on one column under certain conditions

I want to import records from Gmail into a table, and I do not need duplicates for each account.

Description:

I have a table named list with definition below:

id int(11),
account_id int(11),
email varchar(255),
phone varchar(30),
primary key(id),
FOREIGN KEY (account_id) REFERENCES accounts (id)

This table holds records for different accounts and an email can be considered valid for two or more accounts. This means that an email can repeat in a table but can only appear once for each account_id.

I imported my contacts from Gmail (which is above 700 contacts and other users may have more than that).

The challenge:

I have an option of running two queries (one to check if email or phone exists, the second to insert record) for each record which in my case is 1,400 SQL queries to enable me insert all imported records, ensuring there are no duplicates for each account_id in the list table.

I have looked at MySQL IGNORE and similar keywords like ON DUPLICATE KEY UPDATE but they do not seem to work in this scenario as I cannot make the email and phone columns unique as they can contain duplicate content.

What is the best way of inserting these 700 records ensuring that the email and phone are not repeated for each account_id without having to run 1,400 queries?

QUESTION UPDATE:

I do not think INSERT IGNORE CAN WORK HERE FOR THE FOLLOWING REASONS:

  1. I cannot make email and phone unique columns
  2. The phone number may be empty but with an email entry, this may break the unique pattern

QUESTION ILLUSTRATION

I have two offices using the table to store their customer records. Someone can be a customer to both offices. This means his record can appear twice in the table but can only appear once for each account_id in the table. The challenge now is to insert several records into the table ensuring that a record does not repeat for each account_id.

Upvotes: 3

Views: 1457

Answers (5)

RandomSeed
RandomSeed

Reputation: 29759

What you are trying to achieve is not very clear to me, but it looks very much like you just need to add some two-columns unique constraints.

  • an email must be unique for one given account_id:
ALTER TABLE your_table ADD UNIQUE (account_id, email);
  • a phone number must be unique for one given account_id:
ALTER TABLE your_table ADD UNIQUE (account_id, phone);

Both indexes may exist at the same time on your table. Either could raise a "duplicate-key violation" error, and would trigger the IGNORE or the ON DUPLICATE clauses of your insertions.

That being said, there is an issue in your structure. You are about to duplicate your customers' details for each account_id they are in business with.

You should have a customers table that contains all your customer's contact details (and only that), another accounts table -- your "offices", if I understand it right -- and finally one relation table to model the n-n relationship between customers and accounts:

CREATE TABLE customers_accounts (
    customer_id INT NOT NULL,
    account_id INT NOT NULL,
    PRIMARY KEY (customer_id, account_id),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    FOREIGN KEY (account_id) REFERENCES accounts(id)
);

Upvotes: 5

Robbie
Robbie

Reputation: 17710

You had the answer: use "INSERT IGNORE" but what you probably didn't do is add a composite unique index (mentioned by RamdomSeed above), and/or set blank fields to NULL.

1) Create composite index, using the account id. This means that the email must be unique for that user.

ADD UNIQUE(account_id, email)

2) Regarding the phone "may be blank" set this to NULL when blank. Unique indexes ignore NULLS. (A small gotcha, but probably plays in your favour here, and why it's like that. You can then also add

ADD UNIQUE(account_id, phone)

(Aside: general advice is that you don't usually have multiple uniques on a table as it can get confusing and messy, but it might be what you need and it's fine - so long as you can handle the logic)

Upvotes: 2

sel
sel

Reputation: 4957

Insert Into YourTable (Id, Account_Id, Email, Phone)
Select a.id, a.Account_Id, a.Email, a.Phone
From (Select t.id, t.Account_Id,  t.Email,  t.Phone from t
   group by account_id,email,phone )a;

Suggest to import the records into a temp table (t). Then only filter the records into another table (yourtable) ie remove the duplicate as you like.

Upvotes: 0

sgeddes
sgeddes

Reputation: 62841

So it sounds like you're using a scripting language (php seems to be popular with mysql) to store an array of contacts from gmail?

If so, this insert statement will insert the record if the account id doesn't exist in the table already -- this uses an Outer Join with a Null check, but you can also use Not In or Not Exists as well:

Insert Into YourTable (Id, AccountId, Email, Phone)
Select t.Id, t.AccountId, t.Email, t.Phone
From (Select 1 Id, 1 AccountId, 'someemail' Email, 'somephone' Phone) t
    Left Join YourTable t2 On t.AccountId = t2.AccountId
Where t2.AccountId Is Null

EDIT:

Assuming I'm understanding the comments, then just add to the Outer Join:

Insert Into YourTable (Id, AccountId, Email, Phone)
Select t.Id, t.AccountId, t.Email, t.Phone
From (Select 1 Id, 1 AccountId, 'someemail' Email, 'somephone' Phone) t
    Left Join YourTable t2 On t.AccountId = t2.AccountId
        And (t.email = t2.email Or t.phone = t2.phone)
Where t2.AccountId Is Null

This should ensure no accounts get reinserted if they have a matching phone or email.

Upvotes: 0

sgeddes
sgeddes

Reputation: 62841

Seems like you could use INSERT IGNORE assuming AccountId is your unique identifier:

INSERT IGNORE INTO table
SET field = someValue,
    anotherfield = someothervalue

If however you can have the same accounts with multiple emails, then this may not be what you're looking for.

Upvotes: 0

Related Questions