Update table attributes from another table

801
12
Jump to solution
07-18-2017 03:00 PM
CCWeedcontrol
Regular Contributor

I have a table(Table1) that i need to update on a regular basis by comparing/matching the records in the  (Table1) to the table (Table2) based on field "Street" of Table1 and "FullStName" of table two. If theirs NOT a match it then updates the table1 based on the NOT matched from the table and I also need it to add "FullStName" have have "RES", "Reserved", "proposed" in the "status" field of table2. I need to add the missing names of tabel2 to table 1. Hopefully it make sense.

This is what i have but i am not sure i am doing it right...

import arcpy
from arcpy import env

arcpy.env.workspace = r"C:\Temp\Default.gdb\RoadNames"
Rtable = "table1"  
table1 = "table2"  
  
fcIncident = []  
tableIncident = []  
  
with arcpy.da.SearchCursor(Rtable, ["Street"]) as cursor:  
    for row in cursor:  
        fcIncident.append(row[0])  
  
del row, cursor  
  
with arcpy.da.SearchCursor(table1, ["FULLSTNAME", "STATUS"]) as cursor:  
    for row in cursor:
        if row[1] in ("RES", "Reserved", "proposed"):
            if not row[0] in fcIncident:  
                tableIncident.append(row[0])  
  
del row, cursor  
  
arcpy.MakeTableView_management(table1, "tblView")  
  
for ID in tableIncident:  
    arcpy.SelectLayerByAttribute_management("tblView", "ADD_TO_SELECTION", "FULLSTNAME = " + str(ID)) 


0 Kudos
1 Solution

Accepted Solutions
MitchHolley1
MVP Regular Contributor

First, set your arcpy.env.workspace to a database, not a feature class.  

Try the code below, and edit line 3 & 15.  If table1 has different field names than table2, simply create a new list variable, 'table2fields', and specify the required fields.  Then, replace 'fields' with 'table2fields' on line 28.  Please read more about InsertCursor and how it functions.  The items in the 'ids' list must be indexed to the field lists in order for the values to be written to the proper fields. 

import arcpy

arcpy.env.workspace = r'...path to database...'

table1 = 'table1'
table2 = 'table2'

table1List = []
with arcpy.da.SearchCursor(table1, ['Street']) as cursor:
    for row in cursor:
        table1List.append(row[0])
del cursor

#list of fields whose will need to be imported into table2
fields = ['FULLSTREET','Input_Other','Necessary_Fields','Here']

#list of values from table1 to inject into table2
ids = []
included = ["RES","Reserved","proposed"]
with arcpy.da.SearchCursor(table2, fields) as cursor:
    for row in cursor:
        if row[0] in included:
            if row[0] not in table1List:
                ids.append(row[0:])
del cursor

#create insert cursor variable
insertCursor = arcpy.da.InsertCursor(table2,fields)

#loop through items in ids list and insert into table
for i in ids:
    insertCursor.insert(i)
del cursor

View solution in original post

12 Replies
DanPatterson_Retired
MVP Esteemed Contributor

more to the question... did it do anything? errors? 

My first thought would be to join table B to table A, run your query where they are not equal, then use a field calculate (Calculate Field) to update the table A values with them from table B

0 Kudos
CCWeedcontrol
Regular Contributor

I get ERROR 000358: Invalid expression.

How do you query where they are not equal to a joined tables?

How do you update the table with only the missing names?

0 Kudos
MitchHolley1
MVP Regular Contributor

Try this: 

EDIT:  Now that I think about this more.. the below code will only select the in-memory layer by the streets that are not in table2.  If you need to add the missing names to table two, like actually add the rows in the table, you'll need to use an InsertCursor—Data Access module | ArcGIS Desktop.  Is this what you're trying to do?

import arcpy

arcpy.env.workspace = r'...path to database'

table1 = 'table1'
table2 = 'table2'

table1List = []
with arcpy.da.SearchCursor(table1, ['Street']) as cursor:
    for row in cursor:
        table1List.append(row[0])
del cursor

ids = []
included = ["RES","Reserved","proposed"]
with arcpy.da.SearchCursor(table2, ['FULLSTREET']) as cursor:
    for row in cursor:
        if row[0] in included:
            if row[0] not in table1List:
                ids.append(row[0])
del cursor

arcpy.MakeTableView_management(table1, "table1view")

qry = "{} IN ('{}')".format("FULLSTREET", "', '".join([x for x in ids]))
arcpy.SelectLayerByAttribute_management("table1view", "NEW_SELECTION",qry)

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
CCWeedcontrol
Regular Contributor

I tried your code and i get ERROR 000358: Invalid expression.

How would you get it to update table 1 with the missing names?

0 Kudos
MitchHolley1
MVP Regular Contributor

What exactly do you mean by "update table 1 with the missing names"?  Do you mean insert the rows into the table?  Or update the old street names with the new ones from table 2?

0 Kudos
CCWeedcontrol
Regular Contributor

Insert the missing names(rows) from table 2 to table 1. Table 1 is the main table that needs to be updated.

0 Kudos
MitchHolley1
MVP Regular Contributor

First, set your arcpy.env.workspace to a database, not a feature class.  

Try the code below, and edit line 3 & 15.  If table1 has different field names than table2, simply create a new list variable, 'table2fields', and specify the required fields.  Then, replace 'fields' with 'table2fields' on line 28.  Please read more about InsertCursor and how it functions.  The items in the 'ids' list must be indexed to the field lists in order for the values to be written to the proper fields. 

import arcpy

arcpy.env.workspace = r'...path to database...'

table1 = 'table1'
table2 = 'table2'

table1List = []
with arcpy.da.SearchCursor(table1, ['Street']) as cursor:
    for row in cursor:
        table1List.append(row[0])
del cursor

#list of fields whose will need to be imported into table2
fields = ['FULLSTREET','Input_Other','Necessary_Fields','Here']

#list of values from table1 to inject into table2
ids = []
included = ["RES","Reserved","proposed"]
with arcpy.da.SearchCursor(table2, fields) as cursor:
    for row in cursor:
        if row[0] in included:
            if row[0] not in table1List:
                ids.append(row[0:])
del cursor

#create insert cursor variable
insertCursor = arcpy.da.InsertCursor(table2,fields)

#loop through items in ids list and insert into table
for i in ids:
    insertCursor.insert(i)
del cursor
CCWeedcontrol
Regular Contributor

my apologies for not being clear and really appreciate you helping me with this. The code you posted looks to inject "RES","Reserved","proposed" fields into table 2, which is not what am after. i am needing to copy road names from table 2 field ( "FULLSTNAME") to table 1 field ( "Street") but only if they don't already  exist in table 1. we can disregard the "RES","Reserved","proposed" i think its making it confusing.

0 Kudos
MitchHolley1
MVP Regular Contributor

Okay, then remove line 19.  Remove line 22.  Dedent ( CTRL + [ ) lines 23 and 24. 

0 Kudos