yea, I think i over thought this . . . agreed,  thanks , Solved

Solved, our data is whacked . . .  we have 500,000 rows in pricing for the 7000 distinct parts in temp 1

thanks

I dont know what a DDL is . .. I just need the COMPOSITE PARTS from ZATS  that match the PART_NUMs in #TEMP1   . . . . . sorry, very...

You guys rock.  I just had two CTEs , , , i didnt do the Select after CTE2 , , , 
Thanks!!

thanks for looking at this.... the suggested query just ran also.  Attached is the execution plan for my query.

thanks  . . .

ok, maybe this is a "correlated subquery" issue???  i dont know

thanks

The CTE runs on its own, and the rest of the SELECT runs its own, FAST.
But when i JOIN them, it runs forever . . 

I hope that...

The problem is in the CTE, but i dont see it . . . 

SELECT
Z.[COMPOSITE_PART]
,Z.HTS_NUMBER
,MAX(Z.CREATED_DATE) AS MAX_DATE
FROM [TSI].[ZATS_BROKER_FEED] Z
INNER JOIN ADHOC.ATS_ESL L...

so now i have this . . . but dont know how to join on this
SELECT DISTINCT    
H.CUSTOMS_ENTRY_NUM as [Entry Num]
,L.Entry_Line_ID as Line
,H.[Broker_Invoice]

i agree that the list FROM probably doesnt belong, so now i have 
UPDATE t
SET  response_sol_id = x.[solicitation_id]
FROM #temp_stp t
   CROSS apply (SELECT...

I noticed the missing ) on Max Sent date line, but still get Syntax error new FROM.

thanks

Thanks SO much you guys.
Drew, thanks for an English language explanation.
I wll give due credit when i cascade to my coworkers, to you and SSC.

SOLVED;

CHRISM@Work's solution was golden.

I really do appreciate the support.

Solved

WOW, i fixed it myself!  (i don't get out much)

I need to put 01 and 01 in FULL quotes . . . "01","02"

SOLVED

You are correct, i had not aliased the column, so here is the new code ...

LEFT JOIN
[TSI].[ZATS_BROKER_FEED] Z
on L.Part_Num = Z.Composite_part and Z.[SUB_ORG] =...