SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to delete a list of emails from a big database.

    I have the following table with approximately 20,000 entries.

    ############################
    # ID # Email # Date #
    ############################
    # 1 # jondoe@widget.com # mm-dd-yy #
    # 2 # bill@aol.com # mm-dd-yy #
    ############################

    I also have a text file containing one email address per line (about 900 lines) that need to be REMOVED from above database.

    How can I write a script or use some kind of MySQL command to automatically delete the ENTIRE row wherever an email from my text file is found anywhere in the database? Remember, the database is BIG.

    Your help is greatly appreciated.

  2. #2
    SitePoint Guru
    Join Date
    Jan 2001
    Location
    Alkmaar, Netherlands
    Posts
    710
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    900 items is not much and 20,000 entries is nothing also( email field is indexed I hope)

    Anyway read few emails addresses into array and implode them with comma and form this query string.

    DELETE FROM bigTable
    WHERE Email IN ('email1@hotmail.com', 'email2@hotmail.com',...)

  3. #3
    SitePoint Wizard Chris82's Avatar
    Join Date
    Mar 2002
    Location
    Osnabrück
    Posts
    1,003
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In MySQL v4 it should be possible to use:

    First, get the text file into a new temp table

    Code:
    -- create temp table
    CREATE TABLE mytemp (emails VARCHAR(100));
    
    -- insert the emails from the file
    LOAD DATA 'emails.txt' 
       INTO TABLE mytemp;
    
    -- get all emails which are both present in the original table and the temp table
    SELECT * FROM emails
    WHERE email IN (
       SELECT email FROM mytemp
    );
    With MySQL v3 this could work:

    Code:
    -- insert the emails from the file
    LOAD DATA 'emails.txt' 
       INTO TABLE emails (email);
    
    -- get all emails which are both present in the original table and the temp table
    CREATE TABLE newtable
    AS 
    SELECT ID, DISTINCT Email, `Date` FROM emails;


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •