How use syntax on SQL Server

Question

I user of pgadmin... I have a problem with SQL Server syntax for spatial joins.

I need this query on SQL Server:

select * 
from table a, table b
where st_within (st_centroid(a.geom), b.geom)

or

update table a 
set x = b.x
from table b
where st_within (st_centroid(a.geom), b.geom)

Answer 1

I believe that you might need something like this:

SELECT * 
FROM table a
JOIN table b ON a.geom.STCentroid().STWithin( b.geom) = 1;

That can be later converted into an UPDATE like this:

UPDATE a 
SET x = b.x
FROM table a
JOIN table b ON a.geom.STCentroid().STWithin( b.geom) = 1;

You can find all the information on MS Docs.