SQL Server; STDistance function not working in update statements

Question

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?

Pரதீப் · Accepted Answer · 2015-12-25T16:37:19.503

2

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

edited Dec 25 '15 at 16:37

answered Dec 25 '15 at 16:25

Pரதீப்

91,748
19
131
172

Good idea but `IsFar` is of type `bit` not `float` – Hossein Narimani Rad Dec 25 '15 at 16:29
Yes, it solves the problem but I didn't expect this much code and I'm surprised why SQL just didn't support that syntax – Hossein Narimani Rad Dec 25 '15 at 16:33
simply its not a valid syntax. Both functionally and technically your query has mistakes. You are trying to assign `@center.STDistance(Location)` to `BIT` column and to check a condition and update you need to use `CASE` or `IIF` statement – Pரதீப் Dec 25 '15 at 16:36
But I was trying to assign `@center.STDistance(Location) > 100000` to bit – Hossein Narimani Rad Dec 25 '15 at 16:37
What about this: `select @center.STDistance(location) > 100000 from EventTable`. this also throw the same exception. – Hossein Narimani Rad Dec 25 '15 at 16:40
Because it is not correct. Whats your expectation when you write `select @center.STDistance(location) > 100000 from EventTable`. What you want to to display in query result – Pரதீப் Dec 25 '15 at 16:43
Maybe I'm looking at the SQL with C# glasses, but I expect to return some 0 or 1 values as the result. – Hossein Narimani Rad Dec 25 '15 at 16:46
1

In `SQL` you need `case` statement to **validate** the condition and return `0` or `1` based on result – Pரதீப் Dec 25 '15 at 16:47

SQL Server; STDistance function not working in update statements

1 Answers1