Justin Justin - 6 months ago 11
SQL Question

Update with data from most current row

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.

UPDATE ST_EXTRA
SET ST_EXTRA.colEMAILDATE = PRICE.DATE_START, ST_EXTRA.colEMAILPRICE = PRICE.PRICE
FROM ST_EXTRA
INNER JOIN PRICE ON ST_EXTRA.NUMBER = PRICE.NUMBER
WHERE colEMAIL = 1

Answer

Instead of inner join, use cross apply:

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;
Comments