Updating Oracle Spatial geometry column using SQL?

Question

I am trying to update a table B geom column with the coordinates from table A. Table A having coordinates in two separate columns x and y. Table B to have the coordinates from Table A into Table B's geom spatial column.

Table A and B have an ID in common for each record.

I usually use FME to do this but this time I'd like to do it using pure SQL.

Any suggestions on how to do it?

Answer 1

This should work if your spatial table is "geo_table" and using SRID=4326, geometry column is "geometry", and coordinates are in table "coord_table" in columns "X" and "Y"

update geo_table a set a.geometry=
(select
SDO_GEOMETRY(2001,
4326,MDSYS.sdo_point_type(b.X,b.Y, NULL),NULL, NULL)
from coord_table b
where a.id=b.id);