Help with this query

Question

Please can you take a look at this query and point out what I'm doing wrong:

update @output
set fromdate = o.fromdate,
    todate = o.todate
from
(
    select fromdate, max(todate)
    from @output as o
    left join status as es on
        o.number = es.empid and
        o.ccode = es.compcode and
        @status = es.status
)

I'm trying to update @output with the record thats contains the max(todate). The error I'm getting is:

Msg 156, Level 15, State 1, Procedure CHP_OR_PENSIONEMPLOYEEENROLMENT_842, Line 138 Incorrect syntax near the keyword 'select'.

Answer 1

Following script should perform the update.

UPDATE  @output
SET     fromdate = om.fromdate
        , todate = om.todate
FROM    @output o
        INNER JOIN (
          SELECT  o.number
                  , o.fromdate, 
                  , todate = MAX(todate)
          FROM    @output as o
                  LEFT OUTER JOIN status as es on
                      o.number = es.empid and
                      o.ccode = es.compcode and
                      @status = es.status
        ) om ON om.number = o.number

Note: I assume o.number is the primary key for @output

Answer 2

DECLARE @output TABLE
        (
        number INT,
        fromdate DATETIME,
        todate DATETIME
        )
INSERT
INTO    @output
VALUES  (1, '2010-01-01', '2011-01-01')
INSERT
INTO    @output
VALUES  (1, '2010-02-01', '2010-11-01')
INSERT
INTO    @output
VALUES  (1, '2010-03-01', '2010-12-01')
INSERT
INTO    @output
VALUES  (2, '2010-01-01', '2011-01-01')
INSERT
INTO    @output
VALUES  (2, '2010-01-01', '2012-01-01')
;
WITH    q AS
        (
        SELECT  todate, MAX(todate) OVER (PARTITION BY number) AS maxtodate
        FROM    @output
        )
UPDATE  q
SET     todate = maxtodate

SELECT  *
FROM    @output