SQL for DB Cleanup Utility

[metaphiz]

We use an e-com package that, unfortunately, adds multiple users under the same email address. We run a mailing every week that depends upon the 'mailing' column (yes/no datatype) of the user record to determine whether to mail or not for a given user. I have to run a SQL query using CF that will eliminate multiples 'yes' checkboxes for users with multiple instances of their email address.

The column of each user record that specifies whether they will receive the mailing or not is called 'mailing' in the 'users' table. The email address column (also in the 'users' table) is called 'email'.

What is the SQL for a query that will deselect all but one 'mailing' checkbox for all users with multiple instances of their email address if more than one are checked? The query must not 'check' the mailing column if it is not already checked at least once.

 

[ecobb]

And your database is....?



Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher

 

[iguessthehandlecheck]

something like this should work:
<cfquery name = 'abc' ....
select distinct email
from users
where mailing = 'yes'
</cfquery>

this should provide only one record of that email address. Probably you would need to update such records to set the mailing flag after the mail is sent, to do that you can use the following after sending the mail within the loop:

<cfquery ....
update users set mailing = flag
where email = '#abc.email#'
and mailing = 'yes'
</cfquery>

Hope this helps.

 

[metaphiz]

My DB is MS Access.