Postgresql query won't work

Question

any help appreciated,a week now and I am stuck -

many thanks if you can.

I added an image of the problem but it's disappeared

    WITH SITESmin as (
SELECT public.acc.Location_Easting_OSGR,    public.acc.Location_Northing_OSGR
FROM   acc Sites ,
ORDER BY  ( acc.Location_Easting_OSGR - Sites.SITE_ETG ) * ( acc.Location_Easting_OSGR - Sites.SITE_ETG ) + (acc.Location_Northing_OSGR - "public"."Sites"."SITE_ETG" ) * (     acc.Location_Northing_OSGR - "public"."Sites"."SITE_NTG" )
LIMIT 1
)
UPDATE ACC
SET acc.Location_Easting_OSGR = SITESmin.acc.Location_Easting_OSGR,
acc.Location_Northing_OSGR = SITESmin.acc.Location_Northing_OSGR
FROM SITESmin;

Here's the error:

Error : ERROR:  syntax error at or near "ORDER"
LINE 4:     ORDER BY  ( acc.Location_Easting_OSGR - Sites.SITE_ETG )...

The ^ carat appears just after the Line 4: colon

Please read meta.stackoverflow.com/questions/285551/… and the accepted answer — user330315
– user330315, Commented Mar 17, 2017 at 7:39
In order to get an answer, you should improve and specify your question, also add related code about the issue. — Levent Divilioglu
– Levent Divilioglu, Commented Mar 17, 2017 at 7:40
Mr. @Anthony500, there are rules and rules must be followed. About helpfulness and schoolchild... this questions problem is childish, Error code you supplied indicates where problem lies. Anyone familiar with SQL would notice reason of the problem immediately except a blind person. a_horse_with_no_name did point out to you that there is a coma there which causes that error, yet you ignore it and are being rude and act like a child . — LongBeard_Boldy
– LongBeard_Boldy, Commented Mar 17, 2017 at 8:00

LongBeard_Boldy · Accepted Answer · 2017-03-17 08:37:43Z

on second look i noticed that this query has several problems.If you are using alias then stick to that alias, you have lots of fields defined wrongly or your query you posted has some missing parts and are not present in your example. and update part looks like is missing where condition .... for example

SELECT public.acc.Location_Easting_OSGR,    public.acc.Location_Northing_OSGR

yet you defined alias "Sites", which by the way is missing "as" syntax, it shouldve been

FROM   acc as Sites 


WITH SITESmin as (
SELECT Sites.Location_Easting_OSGR,    Sites.Location_Northing_OSGR
FROM   acc as Sites --,  <--- this coma was is causing that error, it does not belong there or some code is missing
ORDER BY  (Sites.Location_Easting_OSGR - Sites.SITE_ETG ) * ( Sites.Location_Easting_OSGR - Sites.SITE_ETG ) + (Sites.Location_Northing_OSGR -  Sites.SITE_ETG ) * (     Sites.Location_Northing_OSGR - Sites.SITE_NTG )
LIMIT 1
)
UPDATE ACC
SET acc.Location_Easting_OSGR = SITESmin.Location_Easting_OSGR,
acc.Location_Northing_OSGR = SITESmin.Location_Northing_OSGR
FROM SITESmin

---  missing where condition?
 ;

@Abelisto , It is optional for postgresql, yes. socks and shoes are optional for marathon too. yet its easier to run with them, so is proper query syntax easier to read and understand, reducing possible mistakes. For all i know, that mightve not been an alias but a different table joint and there was missing or misplaced coma

Collectives™ on Stack Overflow

Postgresql query won't work

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related