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)

• PolyGeo: Can you present a code snippet that works up to where you are stuck, please? If you are saying that your code works on a small dataset but not your total, then can you include a few rows of each to illustrate what the input and expected output data looks like too. If you are still open to either a Field Calculator or ArcPy solution then I recommend deciding which this question is about, and researching/asking about the other separately.
• Vince: Since a type error is reported, please edit the question to provide more information about the table structure.
• FelixIP: Using update cursor on joined table is not going to work. Create dictionary from source table 1st, where key=commonFieldValue. Use update cursor for destination table. And yes, forget about field calculator, it's performance is appauling
• Aaron: Looks like you have illegal characters in your field headers (e.g. :).
• Gary: @FelixIP I think that's the way it could work, but unfortunately I couldn't find anything that would explain how the dictionary works. If you could help me with it, I think that would lead me to the solution.

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

• Gary: I tried this code, and it's working too. Sadly I can mark only one answer as accepted. Just one question: If I want to learn more about the dictionaries, where should I look for it? Thank you for your help
• Tony: Thank you 4 Years later this works great. Used this to replace creating a lyr joining tables and Field Calculate. My tool went from 1hour and 30 minutes to finish to 2 mins and 30 seconds. some of the feature classes had 20000+ features.
• CreekGeek: very nice. after wasting a couple of hours trying to get a join/update working, I implemented this dict approach in under 10 minutes. Cheers!

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:

enter image description here enter image description here

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

• Gary: This script is great, Worked like a charm. Just two question: 1 If I want to imply this into my code I only need to replace the "GetParametersAsText" with name of the files/fields, right? 2 If I want to learn more about the dictionaries, where should I look for it? Thank you
• FelixIP: @Gary you are correct in regards to parameters. As for dictionaries, simply search for "dictionary python". Don't go for documentation, it's very academic. I prefer "python for dummies" like myself