Update table using select from the same table + st functions

Question

ArcSDE 9.3.1 SP1, ORACLE 11g

Hi,

I seem to be struggling a bit with the proper sql syntax for the following task.
table A = polygons of administrative boundaries
table B = address points

I need to update table B with the administrative area code (table A) of the area that contains the address point... also, it might happen, that one address point might fall into two (or even more) administrative areas, for me it's fine to take the first one the select command encounters.

something like this doesn't work : 
UPDATE ap x SET x.area_id = (SELECT a.area_id FROM zsj_za a, ap b WHERE (sde.st_intersects(a.shape, b.shape) = 1 AND abody.objectid = x.objectid AND rownum = 1)

returns the following error message : Error starting at line 1 in command:
UPDATE ab x SET x.zsj_id = (SELECT zsj.zsj_id FROM zsj_za zsj, ab abody WHERE (sde.st_intersects(zsj.shape, abody.shape) = 1 AND zsj.okres IN ('Martin') AND abody.objectid = x.objectid))
Error report:
SQL Error: ORA-29902: error in executing ODCIIndexStart() routine
ORA-06521: PL/SQL: Error mapping function
ORA-06522: Unable to load symbol from DLL
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 943
ORA-06512: at "SDE.SPX_UTIL", line 2623
ORA-06512: at "SDE.SPX_UTIL", line 2842
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 282
29902. 00000 -  "error in executing ODCIIndexStart() routine"
*Cause:    The execution of ODCIIndexStart routine caused an error.
*Action:   Examine the error messages produced by the indextype code and
           take appropriate action.

St_functions are running properly if I do pretty simple queries. I reckon it's the problem of the syntax 😉 obviously, I'm missing the concept of updating the table using the select command where the same table appears.... any ideas are more than welcome.

thank you in advance for all your help

MichalGasparovic · Answer

I've been reviewing my posts on the forums and came across this one, so I thought I could share my solution with the rest of you... someone might hopefully find it helpful one day:

well, it never worked with ST_intersects, so I've switched to St_Contains and that one seemed to work. Then I've used the EXISTS keyword, which has later proved to be a crucial part of the statement.
UPDATE stat.AP x SET x.is_alloc = 1, x.SO_ID = 555 WHERE ((x.ZSJ_ID = 353) AND EXISTS(SELECT y.objectid FROM stat.SO y WHERE sde.ST_Contains(y.shape, x.shape) = 1))

this might be a slightly different to what I initially wrote in my first post, but the idea is there