How to de-dupe a field “Email_Adress” using AMPscript

Question

I'm performing a data pull from a source DE, some of the records are being imported more than once.

My Query currently looks for:

SELECT *
FROM XXX_DE_NAME

WHERE 

ID = 'XXXXXX'

The field name is EMAIL_ADRESS, I tried "AND EMAIL_ADDRESS is not = but didn't work.

Would appreciate any guidance.

Thanks.

Answer 1

Is Email the Primary Key? If not, try that first.

Otherwise, you can try breaking out the fields and adding Distinct to the SQL so it becomes:

SELECT Distinct Email_Adress
FROM XXX_DE_NAME

WHERE 

ID = 'XXXXXX'

Answer 2

Distinct will only work as deduplication if every field value is the same. In the case where you have the same email address but other field values are different it wont work.

You can use Partition By, example below. This will deduplicate based on email address and assigns a row number to every email address that exists. For a email address existing multiple times there will be multiple row numbers. I've selected in the where clause where row number = 1, so only 1 record for a email address is selected.

Using the order by you can specify any dedupe requirements. Often if you have a date field you might want to order by date Asc/Desc then the newest/oldest record can be selected for a particular email address.

SELECT
x.SubscriberKey,
x.EMAIL_ADDRESS,
x.RowNumber
FROM
(
    SELECT 
    s.SubscriberKey
    s.EMAIL_ADDRESS,
    ROW_NUMBER() OVER(PARTITION BY s.EMAIL_ADDRESS ORDER BY s.EMAIL_ADDRESS) AS RowNumber
    FROM audience_selection_de s
    Where ID = 'xxxx'
) x
WHERE x.RowNumber = 1

Answer 3

There is an easy workaround for this. In your target DE you can set the field Email_Adress as Primary Key. Then in your SQL query, choose the 'Overwrite' option.

Now when you run something like: SELECT * FROM WHERE

The result will be deduplicated based on email address. I tested this in our own org and it works.

This way you'll have to write far less code. :)