update attribute table from another table based on a query

6552
27
Jump to solution
11-16-2017 12:55 AM
Henryobaseki
Occasional Contributor

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!

Tags (2)
0 Kudos
1 Solution

Accepted Solutions
XanderBakker
Esri Esteemed Contributor

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?

View solution in original post

27 Replies
DanPatterson_Retired
MVP Emeritus

python parser... not sure about how sql handles them but print statements use \ as an literal escape character.

row0 = 'hello'

sql = '"{}" = \'{}\''.format("field", row0)
print(sql)

"field" = 'hello'
0 Kudos
Henryobaseki
Occasional Contributor

Hi Dan,

so how do I add this script to the above one.

Thanks for your help

0 Kudos
DanPatterson_Retired
MVP Emeritus

you replace/modify your SQL_stat = ... line

replace "field" with "OID" and row0 with row[0]

0 Kudos
Henryobaseki
Occasional Contributor

import arcpy
... from arcpy import env
... env.workspace = "C:\Users\L0505857\Data"
... print 'Processing...'
... fc = "Updated_subset.dbf"
... cursor = arcpy.da.SearchCursor(fc, ["OID"])
... for row in cursor:
...     SQL_stat= '"{}" = \'{}\''.format("field", row0)
...     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"

0 Kudos
RebeccaStrauch__GISP
MVP Emeritus

Henry, it helps if you post you code using tips  /blogs/dan_patterson/2016/08/14/script-formatting?sr=search&searchId=75ece207-9a50-4ffd-8c5e-182bd0b...‌ 

This helps everyone to refer to the same line number, and also to see if the indentation is correct in your script.  You can edit your post and the above comment to replace the code.

0 Kudos
DanPatterson_Retired
MVP Emeritus

row[0] 

note the slicing, since the first, and only entry in the row object is the value for the oid field.  Things in python are 0 based when counting

0 Kudos
Henryobaseki
Occasional Contributor

Hi Dan,

I have Table 1 with  fields -  Top, Base, Names, and State all have contents

I have Table 2 with  fields -   similar contents Top, Base, Names, and State, but the state needs to be updated with the state content in table 1

A script to update state in  table 2 if Top, Base and Names Match with table 1 if no match do something

not sure if  the sql statement above will help

0 Kudos
DanPatterson_Retired
MVP Emeritus

not sure what the script is doing, partly because of the format.  I was just trying to point out a possible solution to the question you posted in the last lines.  If the script is functioning as designed, could you provide a listing of some input data and an expected output as example

0 Kudos
Henryobaseki
Occasional Contributor

From the script below

can my sql statement be  to update  State in fc2 = "airports_old.dbf" if Field 1 and Field 2 in ... fc = "Updated_subset.dbf" matches withField 1 and Field 2  in airports_old..dbf" 

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
...

0 Kudos