Hossein Narimani Rad Hossein Narimani Rad - 4 years ago 234
SQL Question

SQL Server; STDistance function not working in update statements

I have a table like this

EventTable (IsFar bit, Location geography)

No Problem with Select statement:
I can simply run this with no error:

DECLARE @center AS GEOGRAPHY = GEOGRAPHY::Point(50, -160, 4326);
SELECT * FROM EventTable WHERE @center.STDistance(Location) > 100000


Not working with Update statement: but when I want to update the
IsFar
column based on its distance from a certain point, it throw exception


Incorrect syntax near '>'.


Here is the code:

DECLARE @center AS GEOGRAPHY = GEOGRAPHY::Point(50, -160, 4326);
UPDATE EventTable SET IsFar = @center.STDistance(Location) > 100000


Anyone can explain why this is not working?

Answer Source

You need to add Where clause

UPDATE EventTable SET IsFar = @center.STDistance(Location) 
where @center.STDistance(Location) > 100000

Based on your comment

UPDATE eventtable 
SET    isfar = CASE 
                 WHEN @center.STDistance(location) > 100000 THEN 1 
                 ELSE 0 
               END 

If you are using Sql Server 2012+ then you can use IIF

UPDATE eventtable 
SET    isfar = IIF(@center.STDistance(location) > 100000, 1, 0)

Replace 0 and 1 in case statement based on your requirement

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download