I am looking for a smart approach to cleaning up a relationship class, which consists of relationships that look at previously deleted objects.

To illustrate, we have db.schema.SITE (spatial), db.schema.STATUS (non-spatial) tables in our multi versioned SDE geodatabase (SQL Server), and a relationship class that relates the two tables called db.schema.SITE__STATUS. However, when I run the following query, it returns over 4000 records:

ss.*, status.*, site.*
db.schema.SITE__STATUS ss
left join db.schema.SITE site  on site.OBJECTID = ss.SITEID
left join db.schema.STATUS status on ss.STATUSID = status.OBJECTID 
ss.statusid not in (select objectid from db.schema.STATUS)
ss.hubsiteid  in (select objectid from db.schema.SITE)

I suspect over time statuses have been deleted but somehow the relationship class failed to get updated. But I don't understand (and would love to understand) how this situation is allowed to happen in the geodatabase in the first place.

One approach to cleaning up would be to remove the returned records from the relationship class using SQL, but I fear this will be filled with GOTCHAs I failed to foresee.

What would be a smart way to approach this cleaning up task?

1 Answer 1


Such query will return the wrong result on a versioned table, unless all versions are posted and the DB compress to 1 state only.

Instead of using the table directly, you need to use its corresponding version view. This view will take care of merging together the base table, the A (add) table and the D (deleted) table.

  • I just changed the origin and destination to the versioned views (none for the relationship class). So this would have returned the DEFAULT version view if i understood it correctly. But still there are over 4000 records where destination records are null. The thing that triggered me writing the sql was an import task failing in ArcGIS when it got to importing this relationship class.
    – LHo
    Jun 23, 2020 at 12:17
  • Also just compressed and reran giving the same numbers. So the delta tables will be empty. I'm pretty sure there's some errors in the relationship class.
    – LHo
    Jun 23, 2020 at 12:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.