I have 2 columns in a table that represent the X and Y coordinates. The values in those columns don't match the actual coordinates and I want to update them with the coordinates values that are selected from the GEOMETRY column. I have been trying the following:

update TABLE1

but I am getting the following error:

ORA-01427: single-row subquery returns more than one row

Any suggestions?

  • add rownum=1 to your query
    – Mapperz
    Commented Sep 23, 2015 at 14:17
  • No, that would populate all rows with the coordinate in row 1.
    – Vince
    Commented Sep 23, 2015 at 14:19
  • @ Mapperz as @Vince said, that populated the table with values from the first row only!
    – GIS_DBA
    Commented Sep 23, 2015 at 14:21

1 Answer 1


The solution depends on the geometry of your layer.

Point Geometries

For points you can access the X and Y co-ordinates directly:

update TABLE1 s1 set X = s1.GEOMETRY.SDO_POINT.x,y=s1.geometry.sdo_point.y

Polygon, Multi-point

SDO_UTIL.GETVERTICES will return all vertices of your geometry, and your previous query would fail if the geometry had more than one vertice, or if t1 had more than one row.

You can use a function to calculate the centroid or label point of the polygon and use that to populate your columns. This is complicated by the way updates with joins work in Oracle.

update table1 t1 set x = 
(select c.centroid.sdo_point.x from 
  (select sdo_geom.sdo_centroid(shape,1) centroid,rowid from table1) c
 where c.rowid=t1.rowid)
  • @LatvianGIS, you're welcome! If you are looking for a great source of info on Oracle spatial then I can recommend Pro Oracle Spatial which I found very useful for me getting started.
    – kes
    Commented Sep 24, 2015 at 9:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.