Help Please

[AdWarm]

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.

[r937]

SELECT emails.emailaddress
  FROM badlist
INNER
  JOIN emails
    ON emails.emailaddress LIKE CONCAT('%',badlist.domain)

[AdWarm]

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.

[r937]

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

[AdWarm]

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.

[r937]

i hoped you would go look up the DELETE syntax in da manual --

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)

[AdWarm]

i hoped you would go look up the DELETE syntax in da manual --

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.

[r937]

did you even look at the DELETE syntax?

it's right there in my previous post

DELETE emails
  FROM badlist
INNER
  JOIN emails
    ON emails.emailaddress LIKE CONCAT('%',badlist.domain)