Compare and delete

[Kristine M]

I have seven tables that contain email addresses from which we run our email
marketing campaigns and one table that contains all of the email addresses
that have chosen to opt out of the email distribution. I need to compare the
email addresses from the seven tables to the one table that contains the
opt-outs and then run a delete query if an email exists in the opt-out table
and in any one of the seven tables.
I was considering an add-in such as CompareDataWiz but I still will need to
know how to write the delete query once I have the data comparisons.
Can anyone help with this? Unfortunately, I am working on a pretty tight
deadline so a quick fix would be greatly appreciated

 

[Michael H]

Hi Kristine.

It sounds like you don't want any of the e-mail addresses in the OptOut
table to exist in any of the seven other tables.

I've successfully tested the query below using two (rather than seven)
tables. You should easily be able to modify it to work with all seven. You
didn't supply any of your table or field names, so you'll have to modify mine
to match yours.


SELECT Table1.Email
FROM Table1
WHERE Email In (SELECT Email from OptOut)

UNION SELECT Table2.Email
FROM Table2
WHERE Email In (SELECT Email from OptOut)


The query above returns all records in Table1 and Table2 where the value in
the Email field matches any of the values in the Email field in the OptOut
table. Just add one more UNION SELECT section for each of your remaining 5
tables, and then delete the records that the query returns.

-Michael

 

[Kristine M]

Thank you sso much - this should do it!

Hi Kristine.

It sounds like you don't want any of the e-mail addresses in the OptOut
table to exist in any of the seven other tables.

I've successfully tested the query below using two (rather than seven)
tables. You should easily be able to modify it to work with all seven. You
didn't supply any of your table or field names, so you'll have to modify mine
to match yours.


SELECT Table1.Email
FROM Table1
WHERE Email In (SELECT Email from OptOut)

UNION SELECT Table2.Email
FROM Table2
WHERE Email In (SELECT Email from OptOut)


The query above returns all records in Table1 and Table2 where the value in
the Email field matches any of the values in the Email field in the OptOut
table. Just add one more UNION SELECT section for each of your remaining 5
tables, and then delete the records that the query returns.

-Michael

 

[Kristine M]

I actually combined the seven datatables into one called All_Tables_Combined
and ran the union query against my opt out table called
Unsubscribe_All_Tables ( the field name in both tables is Email_Address) as
you suggested as follows:

SELECT All_Tables_Combined.Email_Address
FROM All_Tables_Combined
WHERE Email_Address In (SELECT Email_Address FROM Unsubscribe_All_Tables)

UNION SELECT Unsubscribe_All_Tables.Email_Address
FROM Unsubscribe_All_Tables
WHERE Email_Address In (SELECT Email_Address FROM Unsubscribe_All_Tables)

This returned 1206 records that I now want to delete from my
All_Tables_Combined datatable. Can I convert the Union query to a delete
query? If so, how?

Thanks again for your quick response

 

[Kristine M]

I actually combined the seven datatables into one called All_Tables_Combined
and ran the union query against my opt out table called
Unsubscribe_All_Tables ( the field name in both tables is Email_Address) as
you suggested as follows:

SELECT All_Tables_Combined.Email_Address
FROM All_Tables_Combined
WHERE Email_Address In (SELECT Email_Address FROM Unsubscribe_All_Tables)

UNION SELECT Unsubscribe_All_Tables.Email_Address
FROM Unsubscribe_All_Tables
WHERE Email_Address In (SELECT Email_Address FROM Unsubscribe_All_Tables)

This returned 1206 records that I now want to delete from my
All_Tables_Combined datatable. Can I convert the Union query to a delete
query? If so, how?

Thanks again for your quick response