Help with this query

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 Incorrec开发者_如何学JAVAt syntax near the keyword 'select'.

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

UPDATE
  [output]
SET
  todate = MAX([es].todate)  /* I've assumed you want to gather this from [es] */
FROM
  @output as [output] 
LEFT JOIN
  status  as [es]
    on  [es].empid    = [output].number
    and [es].compcode = [output].ccode
    and [es].status   = @status
GROUP BY
  [output].<put your primary key field(s) here>

OR

UPDATE
  [output]
SET
  todate = (
             SELECT
               MAX(todate)
             FROM
               status
             WHERE
                   status.empid    = [output].number
               and status.compcode = [output].ccode
               and status.status   = @status
           )
FROM
  @output AS [output]

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