Calculate field in a joined table in ArcPy

Question

---

EDIT: I was able to solve the problem with the dictionary approach suggested by @BERA. However, I still don't get why it does not work with the CalculateField method.

I'm trying to calculate a field in a joined table in ArcPy. Code snippet below.

The Calculate Field always gives back the error message:

ERROR 000539: Invalid field MAX

When I do the same thing manually one after the other in ArcGIS everything works perfectly well.

poly_riskobj = r"E:\SRRM_Walldorf\Risikoobjekte.gdb\Ro_Mitte"
out_stat_table = r"E:\SRRM_Walldorf\Risikoobjekte.gdb\Ro_Mitte_HQ100"

arcpy.MakeTableView_management(out_stat_table, "table_view")
arcpy.MakeFeatureLayer_management(poly_riskobj, "obj_layer")
arcpy.AddJoin_management("obj_layer", "OBJECTID", "table_view", "OBJECTID_1")

arcpy.CalculateField_management(poly_riskobj, "WSP_HQ100", "!MAX!")

When I checked the fields with arcpy.ListFields I realized that the joined fields are missing in the poly_riskobj fc I'm refering to in the CalculateField. They only exist in the obj_layer. Which makes sense... So I adapted the CalculateField to

arcpy.CalculateField_management("obj_layer", "WSP_HQ100", "!MAX!")

and saved it with CopyFeatures. Hoewever, when I check the result in ArcGIS the field "WSP_HQ100" is not updated with the MAX values.

I don't know what I'm missing. I read through these posts but could't figuere it out:

• Understanding arcpy.CalculateField_management with Join?
• How to Calculate Field in joined table using ArcPy?

Answer 1

Maybe your problem could be the the way you are accessing the table and field names. Try setting Qualified Field Names environment setting.

You can try Join Field which should be easier/require less code:

Joins the contents of a table to another table based on a common attribute field. The input table is updated to contain the fields from the join table. You can select which fields from the join table will be added to the input table.

You can also use a dictionary like this, no need for joins:

import arcpy

poly_riskobj = r"E:\SRRM_Walldorf\Risikoobjekte.gdb\Ro_Mitte"
out_stat_table = r"E:\SRRM_Walldorf\Risikoobjekte.gdb\Ro_Mitte_HQ100"

#Create a dictionary holding the objectid_1 and maxvalues from out_stat_table
datadict = {oid:maxval for oid,maxval in arcpy.da.SearchCursor(out_stat_table, ['OBJECTID_1','MAX'])}

with arcpy.da.UpdateCursor(poly_riskobj, ['OBJECTID','WSP_HQ100']) as cursor:
    for row in cursor: #For each row in poly_riksobj table
        if row[0] in datadict: #Check if this rows OBJECTID is in the dict
            row[1] = datadict[row[0]] #If so, update row[1]/WSP_HQ100
            cursor.updateRow(row) #"Save"

Answer 2

I was able to get what I wanted by using the dictionary approach:

import arcpy

poly_riskobj = r"...\Risikoobjekte.gdb\RO_Mitte"

path =  r"...\Risikoobjekte\Risikoobjekte.gdb"

arcpy.env.workspace = r"...\HWGK"
HQ_List1 = arcpy.ListRasters("WSP_" + "*" + "0.tif")
HQ_List2 = arcpy.ListRasters("WSP_" + "*" + "t.tif")
HQ_List = HQ_List1 + HQ_List2
HQ_name_list = ["WSP_HQ10", "WSP_HQ50", "WSP_HQ100", "WSP_HQEXT"]

for index,i in enumerate(HQ_List):
    out_stat_table = path + "\\" + "RO_Mitte_" + HQ_name_list[index]
    
    ZonalStatisticsAsTable(poly_riskobj, "OBJECTID", i, out_stat_table, "DATA", "MIN_MAX_MEAN")
    
    #Create a dictionary holding the objectid_1 and maxvalues from out_stat_table
    datadict = {oid:maxval for oid,maxval in arcpy.da.SearchCursor(out_stat_table,['OBJECTID_1','MAX'])}

    with arcpy.da.UpdateCursor(poly_riskobj, ['OBJECTID',HQ_name_list[index]]) as cursor:
        for row in cursor: #For each row in poly_riksobj table
            if row[0] in datadict: #Check if this rows OBJECTID is in the dict
                row[1] = round(datadict[row[0]],2) #If so, update row[1]/WSP_HQx
                cursor.updateRow(row) #"Save"