Copy an attribute value from one table to another, based on matching key attributes

[MatthewWalker1]

I am trying to write a python script to copy an attribute value from one table to an attribute in another table, based on a primary key to foreign key match.  Basically, this is a workaround for relating an '_ATTACH' table to its parent table's record (the attachment record was created outside of Arc, so the 'REL_OBJECTID' attribute does not automatically get populated with the parent tables' record's 'OBJECTID', so I am trying to systematically repair this link).  I cannot figure out the syntax in using an updatecurser (or even if this is the way to do this!).

Let's say that I have a table, 'Table1' with a primary key, PK_GUID, field that is populated, and it has a system created attachment table, 'Table1_ATTACH' with a foreign key, FK_GUID, populated with the parent's primary key value (this is the link I can use in my where clause to link the records):

I need for the attribute Table1_ATTACH.REL_OBJECTID to be populated with Table1.OBJECTID

I attempted to write a script to do this, mimicking some bad code I found in my google search:

#define the parent table Table2 = r"database connections\connection to db\db.DBO.Table1__ATTACH"  #create a cursor on the child table's record that is linked to the parent table's record on the non-ESRI keys Rows = arcpy.UpdateCursor("db.DBO.Table1__ATTACH", "select * from db.DBO.Table1 where db.DBO.Table1.PK_GUID = db.DBO.Table1_ATTACH.FK_GUID", "", "REL_OBJECTID") for row in rows:  row.REL_OBJECTID = T2.OBJECTID 

Am I anywhere near close on this one?

[JakeSkinner · Accepted solution]

Hi Matt,

It's actually a little tricky to calculate field values from one table to another outside of ArcMap.  Here is some sample code that I was able to get to work:

import arcpy from arcpy import env env.workspace = r"C:\temp\python\test.gdb"  fc = "Airports" table = "Airports__ATTACH"  # Add a field to the feature class and calculate the OBJECTIDs to this new field arcpy.AddField_management(fc, "OBJECTID2", "LONG") arcpy.CalculateField_management(fc, "OBJECTID2", "!OBJECTID!", "PYTHON_9.3")  # Join the new OBJECTID2 field to the attachment table (original OBJECTID will not join) arcpy.JoinField_management(table, "FK_GUID", fc, "PK_GUID", "OBJECTID2")  # Update the REL_OBJECTID field with the OBJECTID2 values rows = arcpy.UpdateCursor(table) for row in rows:     row.REL_OBJECTID = row.OBJECTID2     rows.updateRow(row)  del row, rows  # Delete the OBJECTID2 fields from the feature class and table arcpy.DeleteField_management(fc, "OBJECTID2") arcpy.DeleteField_management(table, "OBJECTID2")

[MatthewWalker1]

Hi Matt,

It's actually a little tricky to calculate field values from one table to another outside of ArcMap.  Here is some sample code that I was able to get to work:

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

fc = "Airports"
table = "Airports__ATTACH"

# Add a field to the feature class and calculate the OBJECTIDs to this new field
arcpy.AddField_management(fc, "OBJECTID2", "LONG")
arcpy.CalculateField_management(fc, "OBJECTID2", "!OBJECTID!", "PYTHON_9.3")

# Join the new OBJECTID2 field to the attachment table (original OBJECTID will not join)
arcpy.JoinField_management(table, "FK_GUID", fc, "PK_GUID", "OBJECTID2")

# Update the REL_OBJECTID field with the OBJECTID2 values
rows = arcpy.UpdateCursor(table)
for row in rows:
    row.REL_OBJECTID = row.OBJECTID2
    rows.updateRow(row)

del row, rows

# Delete the OBJECTID2 fields from the feature class and table
arcpy.DeleteField_management(fc, "OBJECTID2")
arcpy.DeleteField_management(table, "OBJECTID2")

I'm not sure what I am doing wrong here, I took your code, changed the env.workspace and featureclass and ATTACH table and tried to run it through the cmd prompt, and keep getting an error stating the dataset does not exist.  Here is my code:

import arcpy
from arcpy import env
env.workspace = "database connections\connection to db\Fire.DBO"

fc = "Event"
table = "Event__ATTACH"
# Add a field to the feature class and calculate the OBJECTIDs to this new field
print "attempting to add field..."
arcpy.AddField_management(fc, "OBJECTID2", "LONG")
print "added field 'OBJECTID2'..."
arcpy.CalculateField_management(fc, "OBJECTID2", "!OBJECTID!", "PYTHON_9.3")
print "Calculated field..."
# Join the new OBJECTID2 field to the attachment table (original OBJECTID will not join)
arcpy.JoinField_management(table, "FK_GUID", fc, "PK_GUID", "OBJECTID2")

# Update the REL_OBJECTID field with the OBJECTID2 values
rows = arcpy.UpdateCursor(table)
for row in rows:
    row.REL_OBJECTID = row.OBJECTID2
    rows.updateRow(row)

del row, rows

# Delete the OBJECTID2 fields from the feature class and table
arcpy.DeleteField_management(fc, "OBJECTID2")
arcpy.DeleteField_management(table, "OBJECTID2")

and here is the error message, from the cmd prompt:


C:\Users\matt\Desktop>AttachTableJoins.py
attempting to add field...
Traceback (most recent call last):
  File "C:\Users\matt\Desktop\AttachTableJoins.py", line 9, in <module>
    arcpy.AddField_management(fc, "OBJECTID2", "LONG")
  File "C:\Program Files (x86)\ArcGIS\Desktop10.0\arcpy\arcpy\management.py",
ne 2625, in AddField
    raise e
arcgisscripting.ExecuteError: Failed to execute. Parameters are not valid.
ERROR 000732: Input Table: Dataset Event does not exist or is not supported
Failed to execute (AddField).


I know the tables are there and there are no feature datasets in the database, so I don't know what that error message is coming from.

[JakeSkinner]

Try changing your workspace to the following:

env.workspace = "database connections\connection to db.sde"

You are missing the '.sde' for the connection file, and you will not have to specify the database name and owner.  Python will use the credentials stored within the SDE connection file.

[MatthewWalker1]

Try changing your workspace to the following:

env.workspace = "database connections\connection to db.sde"

You are missing the '.sde' for the connection file, and you will not have to specify the database name and owner.  Python will use the credentials stored within the SDE connection file.

That. Is. Awesome!  Thank you, that resolved my issue and I was able to run the script!  This gets me one step closer to utilizing the 'Attach' table and photo display support I am looking for.  Thanks again for your insight into this issue!