Update a File Geodatabase using an SQL Join

1629
2
01-05-2013 03:18 PM
EdwardBlair
Occasional Contributor
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
0 Kudos
2 Replies
JakeSkinner
Esri Esteemed Contributor
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.
0 Kudos
EdwardBlair
Occasional Contributor
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
0 Kudos