topic Re: Update a File Geodatabase using an SQL Join in Data Management Questions

Update a File Geodatabase using an SQL Join

EdwardBlair — Sat, 05 Jan 2013 23:18:16 GMT

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

Re: Update a File Geodatabase using an SQL Join

JakeSkinner — Mon, 07 Jan 2013 10:25:23 GMT

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.

Re: Update a File Geodatabase using an SQL Join

EdwardBlair — Mon, 07 Jan 2013 21:38:05 GMT

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