updating e-market restrictions nightly job

Hi friends,

Does anyone have any code they can share re: updating nightly e-market restrictions that does not override patrons who opt back into receiving emails?  It turns out the code we have been using only updates records that have NULL as the value and we need them to override any and ALL restrictions, but only if it has not been manually opted back in, inherited via a merge, or if the patron has provided a new primary email address.  We are still in v11, so maybe some of this will be different in v12, but I'd like to wrap my head around it if I can.

Here's the code we currently have that runs every night:

USE Impresario

UPDATE T_CUSTOMER

SET eMarket_ind = 2

WHERE Customer_no IN

(Select DISTINCT c.customer_no, p.response, p.campaign_no

FROM T_CUSTOMER AS c

JOIN VS_PROMOTION AS p ON p.Customer_no = c.Customer_no

WHERE c.eMarket_ind IS NULL

AND p.Response IN ( 12 )

)

Here's what I put together just now, but my results are of people who have unsubscribed a long time ago, but have opted back in by creating a new primary email address either on our website or via the phone.  I don't want to re-unsubscribe those people.  I want to bypass them. Anyone else have any history with this or have had to fix something like this? :)  I'm still new to SQL so I get lost sometimes...

USE Impresario

UPDATE T_CUSTOMER

SET eMarket_ind = 2

WHERE Customer_no IN

(

SELECT DISTINCT c.customer_no

FROM T_CUSTOMER AS c

JOIN VS_PROMOTION AS p ON p.Customer_no = c.Customer_no

where p.Response IN ( 12 )

and p.media_type = 8

and c.emarket_ind IN (1, 3, 4, 5, 6)

)