Update a File Geodatabase using an SQL Join

[EdwardBlair]

Hello -

I've got a file geodatabase on which I need to perform a mass update through a join.  I could do this through a cursor, but there are millons of row to update and the process would take quite a while.

If I were to do this in a Personal Geodatabase I would drop into Access, create the following query and execute it:

Update GasServiceOrder, GasService
Set GasServiceOrder.GasServiceObjectID = GasService.ObjectID
Where GasServiceOrder.OldKey = GasService.OldKey

This works fine and quickly in Access.

A similar syntax executed within IWorkspace.ExecuteSQL() returns a syntax error.

Any suggestions would be much appreciated.

Thanks,
Ed Blair

[JakeSkinner]

Hi Ed,

Are you using ArcGIS 10.1?  If you are, you can use the new data access module.  Using arcpy.da.UpdateCursor is much faster than the regular arcpy.UpdateCursor.  A thread found here shows a user was able to update half a billion records in one hour with the arcpy.da.UpdateCursor.

[EdwardBlair]

Jake -

Thanks for the suggestion on update cursors.   I tried this (within ArcObjects) and it worked, but it wasn't so fast.   Maybe you were suggesting using python.

At any rate, I didn't have a lot of time to futz so I created a dictionary linking the old key to the new key then updated by class using look-ups to the dictionary.  Maybe a little more effort than I was expecting, but it works and works quite fast over a large number of rows.

Thanks again for the suggestion!
Ed