Untitled
raw download clone
SQL
views 109
,
size 1005 b
with suppliers_without_orgs AS (
	select s.id as supplier_id, 
	       s.emails
	from supplier_user su
	left join supplier s on s.id  = su.supplier_id
	left join supplier_org org on org.supplier_id = s.id
	where org.id is null and s.default_contract_id is null and s.emails <> ''
	group by s.id
),

suppliers as (select s.id, s.emails
from supplier s
join supplier_org org on org.supplier_id = s.id
join supplier_contract sc on sc.org_id = org.id
left join supplier_user su on su.supplier_id = s.id
where sc.data->>'market_type' = 'v3'
  and s.emails in (select emails from suppliers_without_orgs)
  and su.supplier_id is null
group by s.id),

result as (select swo.supplier_id as old_supplier,
	   swo.emails as old_emails,
	   s.id as new_supplier,
	   s.emails as new_emails
from suppliers s
join suppliers_without_orgs swo on swo.emails = s.emails
)

update supplier_user
set supplier_id = t.new_supplier
from result t
where supplier_user.supplier_id = t.old_supplier;
close fullscreen
Login or Register to edit or fork this paste. It's free.