Adding and updating field from tables

TheophileEmmanouilidis

Dear all,
I am currently trying to compute a lot of shortest paths between about 10'000 locations with network analyst (OD Cost Matrix). Because of long calculation time, I�??ve decided to run the analysis on multiple smaller dataset (~1'000 locations).  Now let�??s say I have the following tables :

Table 1 field:
Name_ID

Table 2 fields:
Name_ID / distance

Table 3 fields :
Name_ID / distance

Table 1 has been generated by a python script and contains all the possible « Name_ID ». Based on the « Name_ID » field, I want to add (or update) the field  « distance »  (Table 2 and Table 3) to Table 1.

If I use a join to do this I got the following table :
Table_1.Name_ID / Table_2.Name_ID / Table_2.distance / Table_3.Name_ID / Table_3.distance /

And I want to have :
Name_ID / distance

Can someone give me an advice on how to do this ? Can I do a SQL left join ? Is it possible to update an existing table with the "make query table" ?

Thanks in advance,
Regards,
Theo

ps. I'am on ArcGIS 10

jskinner

If I am understanding you correctly, you are trying to sum the two distance fields from 'Table_2' & 'Table_3' and show the sum in 'Table_1'.  To do this, you can add a new field to 'Table_1' called 'Distance' and then use a python script to update this field by summing the Distance fields from 'Table_2' & 'Table_3'.  Here is an example on how to do this:

import arcpy, os
from arcpy import env
env.workspace = r"C:\temp\python\test.gdb"
env.overwriteOutput = True

# Add Distance field if it does not exist
lstFields = arcpy.ListFields("Table_1")
for field in lstFields:
    if field.name == "Distance":
        ""
    else:
        arcpy.AddField_management("Table_1", "Distance", "Double")

# Join tables
arcpy.MakeTableView_management("Table_1", "Table_1_View")
arcpy.AddJoin_management("Table_1_View", "Name_ID", "Table_2", "Name_ID")
arcpy.AddJoin_management("Table_1_View", "Table_1.Name_ID", "Table_3", "Name_ID")

# Update Distance field    
arcpy.CalculateField_management("Table_1_View", "Table_1.Distance", "!Table_2.Distance! + !Table_3.Distance!" , "PYTHON")

print "Successful"