SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: Help Please

  1. #1
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    227
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help Please

    Hi Everyone,

    I need some help please.

    I have a table called Emails and a table called BadList.

    In the table Emails it has 1 field called EmailAddress which has over 300,000 email addresses.

    In the table BadList it has 1 field called Domain which has around 500 domains like "@123.co.uk".

    I'm not sure how to do what i need to do so I need your help please. I need a MySQL Query that will list all the email addresses found in the table Emails that match any of the domains listed in the table BadList.

    I know your going to have to use the WHERE EmailAddress LIKE '%BadListValue%'

    If anyone could help me that would be great.

    Thank you.
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT emails.emailaddress
      FROM badlist
    INNER
      JOIN emails
        ON emails.emailaddress LIKE CONCAT('%',badlist.domain)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    227
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT emails.emailaddress
      FROM badlist
    INNER
      JOIN emails
        ON emails.emailaddress LIKE CONCAT('%',badlist.domain)
    Thank you for your help, that does what i need it to do. But i need them to be deleted from but if i change SELECT to DELETE i get the following error but it works fine if i do it as SELECT.

    #1109 - Unknown table 'Email' in MULTI DELETE

    Any help please?

    Thank you.
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by AdWarm View Post
    Any help please?
    SELECT and DELETE are different syntaxes

    and if the table is called emails, like in your last post, then of course referring to it as Email will cause an error

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    227
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    SELECT and DELETE are different syntaxes

    and if the table is called emails, like in your last post, then of course referring to it as Email will cause an error

    Hi,

    Thanks for your response, sorry my fault i should start again. Below is the query im using.

    DELETE emails.Email
    FROM badlist
    INNER
    JOIN emails ON emails.Email LIKE CONCAT( '%', badlist.domain )
    LIMIT 0 , 2000
    emails is the table name and Email is the field name. Everything works fine if i use the SELECT function, but if i just change the SELECt to DELETE then thats the error i get. I'm not sure why its looking for a table called Email as im saying the table is called emails and the field is called Email.
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i hoped you would go look up the DELETE syntax in da manual --
    Code:
    DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
        tbl_name[.*] [, tbl_name[.*]] ...
        FROM table_references
        [WHERE where_condition]
    notice that you cannot specify a column name in this syntax, just a table name (or alternatively a table name along with the dreaded, evil select star option)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    227
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i hoped you would go look up the DELETE syntax in da manual --
    Code:
    DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
        tbl_name[.*] [, tbl_name[.*]] ...
        FROM table_references
        [WHERE where_condition]
    notice that you cannot specify a column name in this syntax, just a table name (or alternatively a table name along with the dreaded, evil select star option)
    Hey,

    Thanks again for your response, but im confused and not sure what i need to do it delete them, there's over 1700 records when i run the SELECT command, but i need those records delete that match the select query. I've tried selecting them all in PHPMYADMIN and deleting them but that doesnt work, iv tried exporting them, that doesnt work. I'm stuck

    Sorry to be a pain in da ass r937.
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    did you even look at the DELETE syntax?

    it's right there in my previous post

    Code:
    DELETE emails
      FROM badlist
    INNER
      JOIN emails
        ON emails.emailaddress LIKE CONCAT('%',badlist.domain)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •