Need query to remove subscriptions

[Silver_2000_)!]

Im hoping someone here can help with a query

I have about 2200 users who were using AOL email addresses and the site owner decided he didnt want to deal with the bounces and issue swith AOL email so he banned AOL email for signups and also removed the AOL addresses from the user profiles - Which causes a diff problem - subscription emails that bounce because they have no send to email address -
So I need to either remove the subscriptions for the offending users or put logic in the email that checks for valid address before it sends

this query will select the 2200 users that have no email address

SELECT *
FROM `user`
WHERE `email` = CONVERT( _utf8 ' '
USING latin1 )
COLLATE latin1_swedish_ci

How do I remove the subscriptions for those users ?

Thanks in advance

Doug

[Farcaster]

Given you criteria, you could use this statement to delete the subscriptions for these users for MySQL v4.1+:

[SQL]DELETE sf FROM subscribeforum sf, user u WHERE u.userid = sf.userid AND u.email = CONVERT( _utf8 ' '
USING latin1 ) COLLATE latin1_swedish_ci;

DELETE se FROM subscribeevent se, user u WHERE u.userid = se.userid AND u.email = CONVERT( _utf8 ' '
USING latin1 ) COLLATE latin1_swedish_ci;

DELETE st FROM subscribethread st, user u WHERE u.userid = st.userid AND u.email = CONVERT( _utf8 ' '
USING latin1 ) COLLATE latin1_swedish_ci;[/SQL]

[Silver_2000_)!]

Thanks for the help - As a follow up how can I find the users with NO email address and replace the blank email address with their username or better yet set all those users as "Awaiting email confirmation"

Im no sql genuis so any help would be appreciated

[Blaine0002]

Im not sure of some exact code, but you could request the emails from everyone, put them into an array,

explode the array with the @ symbol, then get all that have aol.com and run a delete from user where userid = array userid.

[Silver_2000_)!]

The issue is that people DONT update their emails - AND we are talking about thousands of accounts - not going to do this manually

previous admins already removed everyones email addresses - now I want to change the primary usergroup for all the folks with NULL-blank email addresses to
"awaiting email confirmation"

[Blaine0002]

back up your database then
run the query
UPDATE TABLE user set usergroupid = '3' where email = ''

[Silver_2000_)!]

Originally Posted by Blaine0002

back up your database then
run the query
UPDATE TABLE user set usergroupid = '3' where email = ''

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLE user set usergroupid = '3' where email = ''' at line 1

not sure whats wrong

[Blaine0002]

doh my bad

UPDATE user set usergroupid = '3' where email = ''