The best advice I could give you is to normalize your tables. You have an awful design and you're repeating values with no sense. Table B should only have the country and not the continent or value description.

Only if you have no other choice, a CTE or subqueries might help you with your problem. This might be an option.

WITH SampleData( JobNumber, Entry, Tree) AS(

SELECT 'A1234', 1, 'Location' UNION ALL

SELECT 'A1234', 5, 'Middle East' UNION ALL

SELECT 'A1234', 8, 'Egypt' UNION ALL

SELECT 'A5678', 1, 'Location' UNION ALL

SELECT 'A5678', 5, 'Africa' UNION ALL

SELECT 'A5678', 8, 'Egypt' UNION ALL

SELECT 'B1234', 1, 'Location' UNION ALL

SELECT 'B1234', 4, 'Europe' UNION ALL

SELECT 'B1234', 7, 'United Kingdom' UNION ALL

SELECT 'C1234', 1, 'Location' UNION ALL

SELECT 'C1234', 3, 'America' UNION ALL

SELECT 'C1234', 9, 'USA'

)

,Jobs AS(

SELECT JobNumber

FROM SampleData

GROUP BY JobNumber

HAVING MAX( CASE WHEN Tree <> 'Location' THEN Tree END) = 'Egypt'

AND MIN( CASE WHEN Tree <> 'Location' THEN Tree END) = 'Africa'

AND MAX( Tree) = 'Location'

)

UPDATE b SET

somecolumn = 'Somevalue'

FROM TableB b

JOIN Jobs j ON b.JobNumber = j.JobNumber