-- requete sur sous question en fonction question_id / reponse_id et campagne_annonceur_id
insert into coregistration.expandt_auto_non_eligible (expandt_auto_non_eligible_affichage)
select coregistration_reponse_affichage
from coregistration.coregistration_reponse
inner join coregistration.coregistration on coregistration_coregistration_affichage = coregistration_reponse_affichage and coregistration_champ = 1 -- jointure sur candeg
inner join coregistration.association_coregistration_question on association_coregistration_question_id_question = coregistration_reponse_id_question
where coregistration_reponse_id_question = 1020
and coregistration_reponse_id_reponse in ('3390')
and coregistration_campagne_annonceur = 1385
and coregistration_eligible = 1;
-- on passe les lignes traités à refuse hands ciblage 3 pour ne plus les selectionner
update coregistration.coregistration set coregistration_eligible = 3
where coregistration_coregistration_affichage in
(
select coregistration_reponse_affichage
from coregistration.coregistration_reponse
inner join coregistration.coregistration on coregistration_coregistration_affichage = coregistration_reponse_affichage and coregistration_champ = 1 -- jointure sur candeg
inner join coregistration.association_coregistration_question on association_coregistration_question_id_question = coregistration_reponse_id_question
where coregistration_reponse_id_question = 1020
and coregistration_reponse_id_reponse in ('3390')
and coregistration_campagne_annonceur = 1385
and coregistration_eligible = 1
);
-- fin requete sous question


insert into coregistration.expandt_auto_non_eligible (expandt_auto_non_eligible_affichage)
select coregistration_coregistration_affichage
from coregistration.coregistration
where coregistration_campagne_annonceur = 1385
and coregistration_champ = 6
and coregistration_eligible = 1
and (
        coregistration_valeur  not like ('75%')
		and coregistration_valeur  not like ('93400')
		and coregistration_valeur  not like ('92110')
		and coregistration_valeur  not like ('92200')
		and coregistration_valeur  not like ('92300')
		and coregistration_valeur  not like ('93260')
		and coregistration_valeur  not like ('20000')
		and coregistration_valeur  not like ('971')
		and coregistration_valeur  not like ('973')
		and coregistration_valeur  not like ('975')
		and coregistration_valeur  not like ('972')
		and coregistration_valeur  not like ('972')
		and coregistration_valeur  not like ('976')
		and coregistration_valeur  not like ('984')
		and coregistration_valeur  not like ('987')
		and coregistration_valeur  not like ('986')
		and coregistration_valeur  not like ('988')
        -- and coregistration_valeur not like ('%@yopmail.%')
);
-- on passe les lignes traitees a expandtable 3 pour ne plus les selectionner
update coregistration.coregistration set coregistration_eligible = 3
where coregistration_coregistration_affichage in
(
select coregistration_coregistration_affichage
from coregistration.coregistration
where coregistration_campagne_annonceur = 1385
and coregistration_champ = 6
and coregistration_eligible = 1
and (
        coregistration_valeur  not like ('75%')
		 and coregistration_valeur  not like ('94880')
 and coregistration_valeur  not like ('94800')
 and coregistration_valeur  not like ('94700')
 and coregistration_valeur  not like ('94600')
 and coregistration_valeur  not like ('94550')
 and coregistration_valeur  not like ('94520')
 and coregistration_valeur  not like ('94520')
 and coregistration_valeur  not like ('94510')
 and coregistration_valeur  not like ('94500')
 and coregistration_valeur  not like ('94500')
 and coregistration_valeur  not like ('94490')
 and coregistration_valeur  not like ('94480')
 and coregistration_valeur  not like ('94470')
 and coregistration_valeur  not like ('94460')
 and coregistration_valeur  not like ('94460')
 and coregistration_valeur  not like ('94450')
 and coregistration_valeur  not like ('94440')
 and coregistration_valeur  not like ('94440')
 and coregistration_valeur  not like ('94440')
 and coregistration_valeur  not like ('94430')
 and coregistration_valeur  not like ('94420')
 and coregistration_valeur  not like ('94410')
 and coregistration_valeur  not like ('94400')
 and coregistration_valeur  not like ('94390')
 and coregistration_valeur  not like ('94380')
 and coregistration_valeur  not like ('94370')
 and coregistration_valeur  not like ('94370')
 and coregistration_valeur  not like ('94360')
 and coregistration_valeur  not like ('94350')
 and coregistration_valeur  not like ('94340')
 and coregistration_valeur  not like ('94320')
 and coregistration_valeur  not like ('94310')
 and coregistration_valeur  not like ('94300')
 and coregistration_valeur  not like ('94290')
 and coregistration_valeur  not like ('94270')
 and coregistration_valeur  not like ('94260')
 and coregistration_valeur  not like ('94250')
 and coregistration_valeur  not like ('94240')
 and coregistration_valeur  not like ('94230')
 and coregistration_valeur  not like ('94220')
 and coregistration_valeur  not like ('94210')
 and coregistration_valeur  not like ('94200')
 and coregistration_valeur  not like ('94190')
 and coregistration_valeur  not like ('94170')
 and coregistration_valeur  not like ('94160')
 and coregistration_valeur  not like ('94150')
 and coregistration_valeur  not like ('94140')
 and coregistration_valeur  not like ('94130')
 and coregistration_valeur  not like ('94120')
 and coregistration_valeur  not like ('94110')
 and coregistration_valeur  not like ('94100')
 and coregistration_valeur  not like ('94000')
 and coregistration_valeur  not like ('93800')
 and coregistration_valeur  not like ('93700')
 and coregistration_valeur  not like ('93600')
 and coregistration_valeur  not like ('93500')
 and coregistration_valeur  not like ('93470')
 and coregistration_valeur  not like ('93460')
 and coregistration_valeur  not like ('93450')
 and coregistration_valeur  not like ('93440')
 and coregistration_valeur  not like ('93430')
 and coregistration_valeur  not like ('93420')
 and coregistration_valeur  not like ('93410')
 and coregistration_valeur  not like ('93400')
 and coregistration_valeur  not like ('93390')
 and coregistration_valeur  not like ('93380')
 and coregistration_valeur  not like ('93370')
 and coregistration_valeur  not like ('93360')
 and coregistration_valeur  not like ('93350')
 and coregistration_valeur  not like ('93340')
 and coregistration_valeur  not like ('93330')
 and coregistration_valeur  not like ('93320')
 and coregistration_valeur  not like ('93310')
 and coregistration_valeur  not like ('93300')
 and coregistration_valeur  not like ('93290')
 and coregistration_valeur  not like ('93270')
 and coregistration_valeur  not like ('93260')
 and coregistration_valeur  not like ('93250')
 and coregistration_valeur  not like ('93240')
 and coregistration_valeur  not like ('93230')
 and coregistration_valeur  not like ('93220')
 and coregistration_valeur  not like ('93210')
 and coregistration_valeur  not like ('93200')
 and coregistration_valeur  not like ('93190')
 and coregistration_valeur  not like ('93170')
 and coregistration_valeur  not like ('93160')
 and coregistration_valeur  not like ('93150')
 and coregistration_valeur  not like ('93140')
 and coregistration_valeur  not like ('93130')
 and coregistration_valeur  not like ('93120')
 and coregistration_valeur  not like ('93110')
 and coregistration_valeur  not like ('93100')
 and coregistration_valeur  not like ('93000')
 and coregistration_valeur  not like ('92800')
 and coregistration_valeur  not like ('92700')
 and coregistration_valeur  not like ('92600')
 and coregistration_valeur  not like ('92500')
 and coregistration_valeur  not like ('92500')
 and coregistration_valeur  not like ('92430')
 and coregistration_valeur  not like ('92420')
 and coregistration_valeur  not like ('92410')
 and coregistration_valeur  not like ('92400')
 and coregistration_valeur  not like ('92390')
 and coregistration_valeur  not like ('92380')
 and coregistration_valeur  not like ('92370')
 and coregistration_valeur  not like ('92360')
 and coregistration_valeur  not like ('92350')
 and coregistration_valeur  not like ('92350')
 and coregistration_valeur  not like ('92340')
 and coregistration_valeur  not like ('92330')
 and coregistration_valeur  not like ('92320')
 and coregistration_valeur  not like ('92310')
 and coregistration_valeur  not like ('92300')
 and coregistration_valeur  not like ('92290')
 and coregistration_valeur  not like ('92290')
 and coregistration_valeur  not like ('92270')
 and coregistration_valeur  not like ('92260')
 and coregistration_valeur  not like ('92250')
 and coregistration_valeur  not like ('92240')
 and coregistration_valeur  not like ('92230')
 and coregistration_valeur  not like ('92220')
 and coregistration_valeur  not like ('92210')
 and coregistration_valeur  not like ('92200')
 and coregistration_valeur  not like ('92190')
 and coregistration_valeur  not like ('92170')
 and coregistration_valeur  not like ('92160')
 and coregistration_valeur  not like ('92150')
 and coregistration_valeur  not like ('92140')
 and coregistration_valeur  not like ('92140')
 and coregistration_valeur  not like ('92130')
 and coregistration_valeur  not like ('92120')
 and coregistration_valeur  not like ('92110')
 and coregistration_valeur  not like ('92100')
 and coregistration_valeur  not like ('92000')
 and coregistration_valeur  not like ('75116')
 and coregistration_valeur  not like ('75020')
 and coregistration_valeur  not like ('75019')
 and coregistration_valeur  not like ('75018')
 and coregistration_valeur  not like ('75017')
 and coregistration_valeur  not like ('75016')
 and coregistration_valeur  not like ('75015')
 and coregistration_valeur  not like ('75014')
 and coregistration_valeur  not like ('75013')
 and coregistration_valeur  not like ('75012')
 and coregistration_valeur  not like ('75011')
 and coregistration_valeur  not like ('75010')
 and coregistration_valeur  not like ('75009')
 and coregistration_valeur  not like ('75008')
 and coregistration_valeur  not like ('75007')
 and coregistration_valeur  not like ('75006')
 and coregistration_valeur  not like ('75005')
 and coregistration_valeur  not like ('75004')
 and coregistration_valeur  not like ('75003')
 and coregistration_valeur  not like ('75002')
 and coregistration_valeur  not like ('75001')
)
);