Pulling Email address from subscriber list when all I have is the subscriber key

Question

I have located bounce subscribers by using data views, this gives me a subscriber key but not their email address.

I now want to remove these subscribers from Marketing Cloud and it appears the only way to do this is to add these subscribers to a list and then delete the list while checking delete subscribers from the application. In order to upload a list an email address is required.

Now I'm trying to write a query to extract the email address from all subscribers for these subscriber keys

SELECT * FROM _Subscribers
WHERE Subscriberkey = '003i00000024jklrj3'  /* these are fake keys*/
AND Subscriberkey = '003i000000rfjkdhsfdf'

Above works but when I try to pull only email address it is not working and I want to do this so when I make the destination data extension I only have to create 2 fields.

The below does not work.

SELECT [Email Address] FROM _Subscribers
WHERE Subscriberkey = '003i00000024jklrj3'  /* these are fake keys */
AND Subscriberkey = '003i000000rfjkdhsfdf'

First why won't my code above work?

Second, I am going to have to add at least 1,000 lines of code to pull 1,000 subscribers at a time. Is there an easier way to do this?

Thanks for your suggestions

Answer 1

Your code wont work because a SubscriberKey cant have 2 values. You should change it to OR and it will return 2 values (if they exist).

1. Create a new data extension (subscriberKeyMatch) with a column called SubscriberKey and upload
2. Copy your subscriber keys into a csv file
3. Upload this csv into your subscriberKeyMatch

Then write run this query:

SELECT 

sm.SubscriberKey
EmailAddress 

FROM subscriberKeyMatch sm

JOIN _Subscribers s

OJ sm.SubscrberKey= s.SubscriberKey

Download this this data extension, import it to a list then you can delete the subscribers from it.

\Note that in an enterprise 2.0 account, you would have to be the parent business unit for this to work

Alternately, you can download the all subscribers list, Import it into a data extension and filter it by Status = Held or Bounced, download it, and import it back into a a list, then delete. This negates the need for a query

Answer 2

Field name for email in [_Subscribers] is EmailAddress. if you want a list of subscribers with their status = bounced, use:

SELECT SubscriberKey, EmailAddress
FROM [_Subscribers]
WHERE Status LIKE 'bounced'

IF you have a list of bounced SubscriberKeys in another DataExtension eg.[OtherDE], use:

SELECT o.SubscriberKey, s.EmailAddress
FROM [OtherDE] o
JOIN [_Subscribers] s ON s.SubscriberKey = o.SubscriberKey

Lastly, if using [_Subscribers] don't work for either, change them to ent._Subscribers