postgresql syntax error

Question

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(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

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

Eric Johnson · Accepted Answer · 2011-04-06 11:29:56Z

1

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

edited Apr 6, 2011 at 11:29

answered Apr 5, 2011 at 16:38

Eric Johnson

18.2k10 gold badges56 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dale Tuttle Over a year ago

Okay, I changed my query around. but now I am getting a different error... ERROR: ERROR: missing FROM-clause entry in subquery for table "source" Position: 203 Query = 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 SOURCE.JOBNO ) AS source Where T1.jobno = source.jobno

Eric Johnson Over a year ago

') AS source' needs to come before 'GROUP BY SOURCE.JOBNO

user330315 Over a year ago

Why do you link to such an old version of the Postgres manual? I would recommend to use "current" instead of the version number in the URL

ypercubeᵀᴹ · Accepted Answer · 2011-04-05 21:28:34Z

0

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

answered Apr 5, 2011 at 21:28

ypercubeᵀᴹ

116k19 gold badges181 silver badges247 bronze badges

Comments

vol7ron · Accepted Answer · 2011-04-05 21:47:53Z

0

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

answered Apr 5, 2011 at 21:47

vol7ron

42.3k22 gold badges126 silver badges178 bronze badges

Collectives™ on Stack Overflow

postgresql syntax error

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related