Databases
StackOverflow2013
Postsdbo
POUsing MySQL IGNORE on one column under certain conditions
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing MySQL IGNORE on one column under certain conditions
    primarykey
    data
    text
    <p>I want to import records from Gmail into a table, and I do not need duplicates for each account.</p> <p><strong>Description:</strong></p> <p>I have a table named list with definition below:</p> <pre><code>id int(11), account_id int(11), email varchar(255), phone varchar(30), primary key(id), FOREIGN KEY (account_id) REFERENCES accounts (id) </code></pre> <p>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.</p> <p>I imported my contacts from Gmail (which is above 700 contacts and other users may have more than that).</p> <p><strong>The challenge:</strong></p> <p>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.</p> <p>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.</p> <p>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?</p> <p><strong>QUESTION UPDATE:</strong></p> <p>I do not think INSERT IGNORE CAN WORK HERE FOR THE FOLLOWING REASONS:</p> <ol> <li>I cannot make email and phone unique columns</li> <li>The phone number may be empty but with an email entry, this may break the unique pattern</li> </ol> <p><strong>QUESTION ILLUSTRATION</strong></p> <p>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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USOgugua Belonwu
    1. USOgugua Belonwu
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POUsing MySQL IGNORE on one column under certain conditions
      1. USOgugua Belonwu
      1. VTFavorite
    2. VO
      singulars
      1. POUsing MySQL IGNORE on one column under certain conditions
      1. USOgugua Belonwu
      1. VTBountyStart
    3. VO
      singulars
      1. POUsing MySQL IGNORE on one column under certain conditions
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI'm a little confused -- option of running 700 queries or 1400 -- why? You can bring all the results into one table, and then run a single insert statement into your account table to match your needs.
      singulars
      1. POUsing MySQL IGNORE on one column under certain conditions
      1. USsgeddes
    2. COhow do i ensure that there are no duplicates for each account_id in the table?
      singulars
      1. POUsing MySQL IGNORE on one column under certain conditions
      1. USOgugua Belonwu
    3. COWhere is your data coming from? Do you have a text file you're trying to run with 700 insert statements? Or are those 700 rows already in a table and you're just trying to get some/all/subset of them to the account table?
      singulars
      1. POUsing MySQL IGNORE on one column under certain conditions
      1. USsgeddes
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload