Search code examples
sqlsql-serverspatialsqlgeography

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?

Solution

  • 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