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 · Accepted solution]

From the code you posted it looks like you are trying to do something like this:

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

tbl1 = "Updated_subset.dbf"
tbl2 = "airports_old.dbf"

fld_oid = "OID"
fld_state = "STATE"

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

with arcpy.da.UpdateCursor(tbl2, (fld_oid, fld_state)) as curs:
    for row in curs:
        oid = row[0]
        state = row[1]
        if oid in dct:
            row[1] = dct[oid]
            curs.updateRow(row)

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

However, it doesn't make much sense to me. The OID in both tables correspond and using the OID you want to update the State in tbl2 with the one in tbl1?

[Henryobaseki]

Hi  Xander,

I keep getting  runtime errors

>>> import arcpy

... from arcpy import env

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

...

... tbl1 = "Updated_subset.dbf"

... tbl2 = "airports_old.dbf"

...

... fld_oid = "OID"

... fld_state = "state"

...

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

...

... with arcpy.da.UpdateCursor(tbl2, (fld_oid, fld_state)) as curs:

... for row in curs:

... oid = row[0]

... state = row[1]

... if oid in dct:

... row[1] = dct[oid]

... curs.updateRow(row)

...

Runtime error

Traceback (most recent call last):

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

File "<string>", line 11, in <dictcomp>

RuntimeError: A column was specified that does not exist.

>>>

[XanderBakker]

This error indicates that either "OID" or "state" is not a field that you have in your table "Updated_subset.dbf". Can you check?

[Henryobaseki]

The two tables have  state and OID RESID attributes

I want to use the state field  attributes in Updated-subset.dbf to update the state field  attribute in airport.dbf which is currently empty

I  want the update to occur if the OID  and the RESID in both tables match.

I m still getting the same runtime error

Thanks for your help

[XanderBakker]

Strange. Can you run the snippet below and post back what it prints?

import arcpy
tbl1 = r"C:\Users\L0505857\Data\Updated_subset.dbf"
for fld in arcpy.ListFields(tbl1):
    print(fld.name)‍‍‍‍

[Henryobaseki]

OID

OBJECTID

ID

FIELD_NAME

KEY_WELL

WATER_DEPT

BASIN_NAME

TOT_RECOVE

HC_TYPE

OVP_bar

Res_Age

Formation

Lithology

RESID

Regional_s

>>>

[XanderBakker]

There you go, there is no "state" field in the table. This is what causes the error on line 11:

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

On this line, a dictionary is used reading the state field, but if it doesn't exist, it will return an error.

You also mention a field called "RESID" that should be used in the code, but it is not referenced anywhere in the code. Do I understand it correctly that the field "RESID" from  Updated_subset.dbf should match the field "OID" from airports_old.dbf?

Can you also list the field from table airports_old.dbf with this snippet and post these:

import arcpy
tbl2 = r"C:\Users\L0505857\Data\airports_old.dbf"
for fld in arcpy.ListFields(tbl2):
    print fld.name‍‍‍‍

And please explain what field in which table needs to be updated by what information when which condition is met. In case a field should be added before the update, please indicate that too.

[Henryobaseki]

Thanks Xander, I was looking at the wrong table.

Code works!

[XanderBakker]

OK, great, then ignore the additional questions I just posted.

[Henryobaseki]

If I want to add another field RESID to the search cursor

I ' m stuck at dct

see below ......

import arcpy
from arcpy import env
env.workspace = r"C:\Users\L0505857\Data"
tbl1 = "Updated_subset.dbf"
tbl2 = "airports_old.dbf"
fld_oid = "OID"
fld_FIELD_NAME = "FIELD_NAME"
fld_RESID = "RESID"
dct = {r[0]: r[1] for r in arcpy.da.SearchCursor(tbl1, (fld_oid, fld_FIELD_NAME,fld_RESID))}
with arcpy.da.UpdateCursor(tbl2, (fld_oid, fld_FIELD_NAME,fld_RESID)) as curs:
    for row in curs:
        oid = row[0]
        FIELD_NAME = row[1]
        if oid in dct:
            row[1] = dct[oid]
            curs.updateRow(row)