I have this query which does what I need unless there are multiple rows in PRICE... essentially, I need to look at the PRICE.DATE_START and determine which has the most recent start date and use that date in my update clause. I've tried several variations of CTE, etc without success. Any feedback is appreciated.
SET ST_EXTRA.colEMAILDATE = PRICE.DATE_START, ST_EXTRA.colEMAILPRICE = PRICE.PRICE
INNER JOIN PRICE ON ST_EXTRA.NUMBER = PRICE.NUMBER
WHERE colEMAIL = 1
inner join, use
UPDATE e SET colEMAILDATE = p.DATE_START, colEMAILPRICE = p.PRICE FROM ST_EXTRA e CROSS APPLY (SELECT TOP 1 p.* FROM PRICE p WHERE e.NUMBER = p.NUMBER ORDER BY DATE_START DESC ) p WHERE colEMAIL = 1;