postgresql syntax error

I trying to create this update statement that adds the average runtimes from today into a temporary table. I keep getting the following syntax error:

ERROR: ERROR: syntax error at or near "INNER" Position: 22

Query = UPDATE temptbl1 AS T INNER JOIN ( select jobno, avg(elapti开发者_如何转开发me) as avgrun from cmr_runinf where to_timestamp(timestmp, 'YYYYMMDDHH24MISS') > (now() - interval '1 DAY') GROUP BY JOBNO ) AS source ON T.jobno = source.jobno SET T.todayrun = source.avgrun

My statement:

UPDATE temptbl1 AS T
INNER JOIN
(
select jobno, avg(elaptime) as avgrun
from cmr_runinf
where to_timestamp(timestmp, 'YYYYMMDDHH24MISS') > (now() - interval '1 DAY')
GROUP BY JOBNO
) AS source
ON T.jobno = source.jobno
SET T.todayrun = source.avgrun

The SET clause should come after the UPDATE clause. Also you probably want a FROM clause in there somewhere.

UPDATE temptbl1 AS T1 
SET T1.todayrun = source.avgrun 
FROM ( 
    SELECT jobno, avg(elaptime) as avgrun 
      FROM cmr_runinf 
     WHERE to_timestamp(timestmp, 'YYYYMMDDHH24MISS') 
           > (now() - interval '1 DAY')
    GROUP BY jobno
) AS source
WHERE T1.jobno = source.jobno

Also see http://www.postgresql.org/docs/current/interactive/sql-update.html

UPDATE temptbl1 AS T1 
SET T1.todayrun = source.avgrun 
FROM ( select jobno
            , avg(elaptime) as avgrun 
       from cmr_runinf 
       where to_timestamp(timestmp, 'YYYYMMDDHH24MISS')
                > (now() - interval '1 DAY')
       GROUP BY jobno     <-- not source.jobno, there is no source inside parenthesis
     ) AS source
WHERE T1.jobno = source.jobno

The way you have it, it's trying to perform the update on a virtual table (join of two physical tables), which isn't permitted.

The SET needs to come after the update