arcpy.da.UpdateCursor on joined tables?

[ErikMartin]

Do data access update cursors work on joined tables?  This thread indicates that Search Cursors do, but I keep getting a "cannot update join table" error.  I have confirmed that I am trying to update the original table, not the joined table.  Here's the pertinent code:

arcpy.MakeFeatureLayer_management(FC, "FC_lyr") FC_lyr = "FC_lyr" arcpy.AddJoin_management(FC_lyr, "joinField", joinedFCTable, "joinField")  fields =("{}.DataField".format(FC_lyr), "{}.OtherDataFiedl".format(joinedFCTable)) where = '"{}.OBJECTID" IS NOT NULL'.format(joinedFCTable)  with arcpy.da.UpdateCursor(FC_lyr, fields, where) as rows:     for row in rows:         row[0] = row[0] + row[1] 

As an aside, I am trying to use the da.UpdateCursor because it is far faster than using field the calculator (code that I already have working) & I am trying to optimize speed.

Thanks,
-Erik

[MathewCoyle]

I've had nothing but problems trying to update fields in a joined table. I would recommend reading this post and examining the attached scripts for ideas on how to get around these issues.

http://forums.arcgis.com/threads/71572-Using-Python-Dictionaries-with-Update-Cursors-instead-of-quot...

I also have a post from a while back using similar methodology in a more specific case example.

http://forums.arcgis.com/threads/55099-Update-cursor-with-joined-tables-work-around-w-dictionaries

[ChrisSnyder]

Using a dictionary and an update cursor in tandem is the solution.... It's actually pretty straight forward:

A basic example in v10.1 syntax that "joins" two fields from the lutTbl to the targetTbl:

lutDict = dict([(r[0], (r[1], r[2])) for r in arcpy.da.SearchCursor(lutTbl, ["SOURCEJOINFIELD","SOURCEFIELD1","SOURCEFIELD2"])])
updateRows = arcpy.da.UpdateCursor(targetTbl, ["TARGETJOINFIELD","TARGETFIELD1","TARGFETFIELD2"])
for updateRow in updateRows:
    joinFieldValue = updateRow[0]
    updateRow[1] = lutDict[joinFieldValue][0]
    updateRow[2] = lutDict[joinFieldValue][1]
    updateRows.updateRow(updateRow)
del updateRow, updateRows

[DavidBrett]

Thanks for posting this. I needed something similar for the results of the Near Analysis and getting the 'near' attributes back to the original feature without using AddJoin_management.

nearDict = dict([(r[0], (r[1])) for r in arcpy.da.SearchCursor(XFMR, ["OBJECTID","EQUIPMENT_ID"])])


with arcpy.da.UpdateCursor(TIGER_Place_Layer, ["NEAR_FID","EQUIPMENT_ID"]) as cursor:
  for row in cursor:
  joinFieldValue = row[0]
  row[1] = nearDict[joinFieldValue]
  cursor.updateRow(row)
  del cursor, row

[ErikMartin]

Thank you both for your prompt responses.  These examples are very helpful.  I've been able to get this working to update field values using joined values, but my understanding of dictionaries is pretty rudimentary at this point... Do either of you have any hints or example of how to do some simple arithmetic?  My two calculations are simply

updateValue = joinValue + updateValue

and

updateValue = joinValue * updateValue

I have yet to get the syntax right using the dictionary method.  Thanks,
-Erik

[ErikMartin]

Thank you both for your prompt responses.  These examples are very helpful.  I've been able to get this working to update field values using joined values, but my understanding of dictionaries is pretty rudimentary at this point... Do either of you have any hints or example of how to do some simple arithmetic?  My two calculations are simply

updateValue = joinValue + updateValue

and

updateValue = joinValue * updateValue

I have yet to get the syntax right using the dictionary method.  Thanks,
-Erik

Would I make 2 dictionaries from 2 SearchCursors?  One on the update data and a second on the joined data?

[MathewCoyle]

Would I make 2 dictionaries from 2 SearchCursors?  One on the update data and a second on the joined data?

I would only look at creating a dictionary of your reference data. You can then cursor through your table you want to update referencing the key in your dictionary with the join field to access the dictionary value. If you don't have much experience with dictionaries I'm sure it is very confusing, but once you try it out they are quite simple.

[ChrisSnyder]

Not sure why you would need to make two dictionaries (maybe if you had two look up tables?).

Dictionaries are a great way to store tabular information... with the concept that you have a "key" field and then a bunch of other values that are associated with the key... sounds like a RDBMS table, right?

dict = {}
dict["cat"] = [1, "fish", 5.7654]
dict["dog"] = [3, "garbage", 15.5]

In this case the key is a string "cat", which is associated with a list of values (age, diet, and weight maybe).
Keys must be unique, but can be strings, integers, floats, or even "tuples" such as a value of  (1,2). Tuple keys can be extreemly usefull.

You retreive values like this:

#get the diet of "cat"
>>> print dict["cat"][1]
'fish'

Dictionaries are great since they are EXTREEMLY fast to access, but a downside is that their size is limited by how much RAM you have.

Anyway to do math per my earlier example:

lutDict = dict([(r[0], (r[1], r[2])) for r in arcpy.da.SearchCursor(lutTbl, ["SOURCEJOINFIELD","SOURCEFIELD1","SOURCEFIELD2"])])
updateRows = arcpy.da.UpdateCursor(targetTbl, ["TARGETJOINFIELD","TARGETFIELD1","TARGFETFIELD2"])
for updateRow in updateRows:
    joinFieldValue = updateRow[0]
    updateRow[1] = updateRow[1] + lutDict[joinFieldValue][0] #assuming these values are numeric and not strings or anything!
    updateRow[2] = updateRow[2] * lutDict[joinFieldValue][1] #assuming these values are numeric and not strings or anything!
    updateRows.updateRow(updateRow)
del updateRow, updateRows

[ErikMartin]

Not sure why you would need to make two dictionaries (maybe if you had two look up tables?).

Dictionaries are a great way to store tabular information... with the concept that you have a "key" field and then a bunch of other values that are associated with the key... sounds like a RDBMS table, right?

dict = {}
dict["cat"] = [1, "fish", 5.7654]
dict["dog"] = [3, "garbage", 15.5]

In this case the key is a string "cat", which is associated with a list of values (age, diet, and weight maybe).
Keys must be unique, but can be strings, integers, floats, or even "tuples" such as a value of  (1,2). Tuple keys can be extreemly usefull.

You retreive values like this:

#get the diet of "cat"
>>> print dict["cat"][1]
'fish'

Dictionaries are great since they are EXTREEMLY fast to access, but a downside is that their size is limited by how much RAM you have.

Anyway to do math per my earlier example:

lutDict = dict([(r[0], (r[1], r[2])) for r in arcpy.da.SearchCursor(lutTbl, ["SOURCEJOINFIELD","SOURCEFIELD1","SOURCEFIELD2"])])
updateRows = arcpy.da.UpdateCursor(targetTbl, ["TARGETJOINFIELD","TARGETFIELD1","TARGFETFIELD2"])
for updateRow in updateRows:
    joinFieldValue = updateRow[0]
    updateRow[1] = updateRow[1] + lutDict[joinFieldValue][0] #assuming these values are numeric and not strings or anything!
    updateRow[2] = updateRow[2] * lutDict[joinFieldValue][1] #assuming these values are numeric and not strings or anything!
    updateRows.updateRow(updateRow)
del updateRow, updateRows

This makes perfect sense... thanks to you both!
-Erik

[ChrisSnyder]

This makes perfect sense

Practice makes perfect... It took me a while to get proficient with dictonaries (aka "hash tables").

However, once I "got it"... one of the most powerfull tricks I have, and allows for some pretty cool stuff.

Some very basic usefull dictionary methods:

dict = {}
dict[1] = ("cat","Elvis")
dict[2] = ("dog","Barky")

>> len(dict) #how many keys?
2

>>> dict.keys() #dumps the keys out to a list
[2,1]

>>> "monkey" in dict #test for the existence of a certain key
False

#print each key and it's 2nd associated value in the dictionary (2nd value is index 1)
for key in dict:
    print str(key) + " - " + str(dict[key][1])

1 - Elvis
2 - Barky