Using UpdateCursor for joined field in ArcPy?

Question

I have a huge point vector data (around 3 million point; in a gdb) with attributes and I have table (created with Near analysis.). I want to copy one of the field values to data. So I joined the table and tried the field calculator to do the trick, but after 16 hours I realized it just froze (it worked with similar data). So the next that could work I think is the UpdateCursor. Unfortunately I got an Error: "TypeError: cannot update join table"

tried this:

field1 = '!temp_Features.leftd!'
field2 = '!leftt:NEAR_DIST!'

with this:

cursor = arcpy.da.UpdateCursor("layer2",(field1,field2))

this:

fields = ['temp_Features:leftd', 'leftt:NEAR_DIST']

with this:

cursor = arcpy.da.UpdateCursor("layer2",fields)

Same error every time.

(I have ArcGIS with advanced license)

Answer 1

Instead of messing with joins, which is a bit confusing and on the slow side, use a python dictionary and an update cursor. Iterate the table you would join, and store the join field values in your dictionary as your key, and the update value as your dictionary value. Then iterate your update table with an update cursor and use the dictionary to update the appropriate field. This will be faster than joining and using a field calculator.

Example code (untested):

#feature class to update
pointFc = r"C:\Some\data.gdb\featureclass"
#update field
updateFld = "UPDATE"
#update fc key field
IdFld = "KEYFIELD"

#join feature class
joinFc = r"C:\Some\data.gdb\joinfeatureclass"
#join value field to be transferred
joinValFld = "VALUE"
#join key field
joinIdFld = "KEYFIELD2"

import arcpy

#create dictionary
#Key: join field
#Value: field with value to be transferred
valueDi = dict ([(key, val) for key, val in
                 arcpy.da.SearchCursor
                 (joinFc, [joinIdFld, joinValFld])])

#update feature class
with arcpy.da.UpdateCursor (pointFc, [updateFld, IdFld]) as cursor:
    for update, key in cursor:
        #skip if key value is not in dictionary
        if not key in valueDi:
            continue
        #create row tuple
        row = (valueDi [key], key)

        #update row
        cursor.updateRow (row)

del cursor

Answer 2

Some time ago I've made this replacement of field calculator. Saved me awful amount of time:

import arcpy, traceback, os, sys
from arcpy import env
env.overwriteOutput = True
layoutFC=arcpy.GetParameterAsText(0)
joinFieldS=arcpy.GetParameterAsText(1)
sourseField=arcpy.GetParameterAsText(2)

destFC=arcpy.GetParameterAsText(3)
joinFieldD=arcpy.GetParameterAsText(4)
destField=arcpy.GetParameterAsText(5)

result=arcpy.GetCount_management(destFC)
nF=int(result.getOutput(0))
arcpy.SetProgressor("step", "", 0, nF,1)

try:
    def showPyMessage():
        arcpy.AddMessage(str(time.ctime()) + " - " + message)
    destFields=arcpy.ListFields(destFC)
    sTable=arcpy.da.TableToNumPyArray(layoutFC,(joinFieldS,sourseField))
    dictFeatures = {}
    for j,s in sTable:dictFeatures[j]=s
    with arcpy.da.UpdateCursor(destFC, (joinFieldD,destField)) as cursor:
        for j,s in cursor:
            arcpy.SetProgressorPosition()
            try:
                v=dictFeatures[j]
                cursor.updateRow((j,v))
            except: pass
except:
    message = "\n*** PYTHON ERRORS *** "; showPyMessage()
    message = "Python Traceback Info: " + traceback.format_tb(sys.exc_info()[2])[0]; showPyMessage()
    message = "Python Error Info: " +  str(sys.exc_type)+ ": " + str(sys.exc_value) + "\n"; showPyMessage()            

Interface:

Please note if no join found for some record, value in calculated field kept unchanged.