with badcontracts as ( select do.number [DONumber], do.copy_name [DOCopyName], do.sequence [DOSeq], cc.number [CompanyNumber], ccl.upstream_division_order_anchor_id [BadDOAnchor], ccl.contract_anchor_id [BadContractAnchor], ccl.upstream_ownership_cycle_id [BadcycleID], ccl.from_date [CCLFrom], ccl.to_date [CCLTo], uoc.revenue [RevCycle], uoc.jib [JIBCycle], dopt.name [DOProduct], odt.name [Description] from upstream_ownership_cycle_contract_links ccl JOIN upstream_division_orders do on ccl.upstream_division_order_anchor_id = do.anchor_id JOIN upstream_ownership_cycles uoc on ccl.upstream_ownership_cycle_id = uoc.id JOIN company_codes cc on do.company_code_id = cc.id JOIN upstream_division_order_product_types dopt on uoc.upstream_division_order_product_type_id = dopt.id JOIN upstream_ownership_description_types odt on uoc.upstream_ownership_description_type_id = odt.id where (ccl.contract_anchor_id is NULL OR ccl.contract_anchor_id = ' ') and do.to_date = '9999-12-31' and do.copy_name IN ('original', 'archived') ), goodcontracts AS ( Select ccl.upstream_ownership_cycle_id [goodcycle],do.number [gooddo], do.sequence [goodseq], cc.number [company], do.copy_name [goodcopyname], do.anchor_id [goodDOAnchor], ccl.contract_anchor_id [goodanchor], c.number [goodContractNumber], c.name [goodContractName] from upstream_ownership_cycle_contract_links ccl join upstream_division_orders do on ccl.upstream_division_order_anchor_id = do.anchor_id join company_codes cc on do.company_code_id = cc.id join contracts c on ccl.contract_anchor_id = c.anchor_id where (ccl.contract_anchor_id IS NOT NULL AND ccl.contract_anchor_id <> ' ') and do.to_date = '9999-12-31' and do.copy_name IN ('archived', 'original') ) update badcontracts set BadContractAnchor = goodanchor from badcontracts bc join goodcontracts gc on bc.[DONumber] = gc.[gooddo] where bc.[Description] = 'Revenue' and goodContractName NOT LIKE '%JIB%'