CODEWITHSUNDEEP
postgresql
Anthony500
Anthony500

Reputation: 11

Postgresql query won't work

query

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

Upvotes: 0

Views: 87

Answers (1)

LongBeard_Boldy
LongBeard_Boldy

Reputation: 812

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?
 ;

Upvotes: 1

Related Questions