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!

[DanPatterson_Retired]

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'‍‍‍‍‍‍

[Henryobaseki]

Hi Dan,

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

Thanks for your help

[DanPatterson_Retired]

you replace/modify your SQL_stat = ... line

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

[Henryobaseki]

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"

[RebeccaStrauch__GISP]

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.

[DanPatterson_Retired]

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

[Henryobaseki]

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

[DanPatterson_Retired]

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

[Henryobaseki]

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