update attribute table from another table based on a query

[Henryobaseki]

Here is my script

import arcpy
... from arcpy import env
... env.workspace = "C:\\Data"
... print 'Processing...'
... fc = "Updated_subset.dbf"
... cursor = arcpy.da.SearchCursor(fc, ["OID"])
... for row in cursor:
...     SQL_stat= "OID = "+ str(row[0])
...     fc2 = "airports_old.dbf"
...     cursor2 = arcpy.da.SearchCursor(fc, ["STATE"], SQL_stat)
...     for row2 in cursor2:
...         UpdatedValue = row2[0]
...         cursor3 = arcpy.da.UpdateCursor(fc2, ["STATE"],SQL_stat)
...         for row3 in cursor3:
...             row3[0] = UpdatedValue
...             cursor3.updateRow(row3)
... del row
... del cursor
... del row2
... del cursor2
... del row3
... del cursor3
...
... print "Done"

At the moment the script below  the OID is coneverted to string

SQL_stat= "OID = "+ str(row[0])

How do I rewrite the SQL stat if the OID Field is already a string, hence, don't need to convert to string

Thanks for your help!

[XanderBakker]

I'm not entirely sure what you want to achieve. I notice that the dictionary you create includes an additional field in the list of field, but for instance RESID is not being taken into account by the dictionary. If you need both attributes for each OID, you could create a list as value in the dictionary like this:

dct = {r[0]: [r[1], r[2]] for r in arcpy.da.SearchCursor(tbl1, (fld_oid, fld_FIELD_NAME, fld_RESID))}‍

So each OID will have a list of [FIELD_NAME, RESID]. But you would have to do something with it in order to justify including it in the dictionary.

I also notice that you are reading the FIELD_NAME in this line:

FIELD_NAME = row[1]‍

.. but you are not doing anything with it. You just overwrite the result if the OID is in the dictionary.

So perhaps a little more explanation on what you are trying to achieve would be necessary for me to understand your goal.

[Henryobaseki]

your code works fine, I just replaced state with Field_name that's all.

I have field_Name and RESID as field attributes in both tables

But what I want to achieve is... if   fid_RESID in tbl 1  equals or matches  the  fid_RESID  in tbl2 then update  fid _Field_Name in tbl2

[XanderBakker]

From what I understand, the code would become something like this (to be sure run this on a copy of the data):

• the dictionary based on tbl1 uses RESID as key and FIELD_NAME and value
• The update cursor runs on tbl2, reads the RESID and if it is in the dictionary update FIELD_NAME

import arcpy
from arcpy import env
env.workspace = r"C:\Users\L0505857\Data"

tbl1 = "Updated_subset.dbf"
tbl2 = "airports_old.dbf"
fld_RESID = "RESID"
fld_FIELD_NAME = "FIELD_NAME"

dct = {r[0]: r[1] for r in arcpy.da.SearchCursor(tbl1, (fld_RESID, fld_FIELD_NAME))}

with arcpy.da.UpdateCursor(tbl2, (fld_RESID, fld_FIELD_NAME)) as curs:
    for row in curs:
        resid = row[0]
        if resid in dct:
            row[1] = dct[oid]
            curs.updateRow(row)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[Henryobaseki]

Morning Xander,

ran the script

Runtime error

Traceback (most recent call last):

File "<string>", line 16, in <module>

NameError: name 'oid' is not defined

[DanPatterson_Retired]

oid ... hasn't been defined there is something missing in Xander's script where oid is defined

[XanderBakker]

Indeed, sorry my bad. Changed the code to take out the oid, but is (was) still referenced on line 16. See changed code below:

import arcpy
from arcpy import env
env.workspace = r"C:\Users\L0505857\Data"

tbl1 = "Updated_subset.dbf"
tbl2 = "airports_old.dbf"
fld_RESID = "RESID"
fld_FIELD_NAME = "FIELD_NAME"

dct = {r[0]: r[1] for r in arcpy.da.SearchCursor(tbl1, (fld_RESID, fld_FIELD_NAME))}

with arcpy.da.UpdateCursor(tbl2, (fld_RESID, fld_FIELD_NAME)) as curs:
    for row in curs:
        resid = row[0]
        if resid in dct:
            row[1] = dct[resid]
            curs.updateRow(row)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

It might be better to attach some sample data, that way I can test the script before publishing and making these kind of mistakes...

[Henryobaseki]

Thank you, it works!

[XanderBakker]

Glad to hear!