Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need to drop all the records from a table with matching field values in a reference table (i.e. lookup table). Do I join the tables first and keep unmatched records? Or is there any other way to do it?

share|improve this question
up vote 5 down vote accepted

I would probably do something like ...

  1. Use Add Field to put a field on your table that will be used to identify those rows that are matched (and need to be deleted).
  2. Join and keep Matched records to calculate a value/flag on them (perhaps 1 rather than 0) different from the unmatched ones.
  3. Remove the Join
  4. Edit the Table and select the rows containing the match value in your added field to Delete those rows.
  5. Delete Field the field you added.
share|improve this answer
Thanks PolyGeo. But it would be nice if I could do it without modifying the existing schema of the table. – Sam Sep 19 '11 at 13:57
OK - instead of doing the Add Field, Calculate Value, Delete Field bits. Just use the Join to select the Matched records and then Switch Selection (to get the unmatched ones) that you can then Start Editing and Delete. – PolyGeo Sep 19 '11 at 21:12

Using a dictionary would be way faster than a list. With a list, you have to look through each value for every row.

lookupTblDic = {}
rows = arcpy.SearchCursor(your lookup table)
update = lookupTblDic.update   
for row in rows:     
    val = row.__getattr__(your field)
del row
del rows

rows = arcpy.UpdateCursor(your drop table)
delete = rows.deleteRow
for row in rows:
    val = row.__getattr__(your field)
    if val in lookupTblDic:
del row
del rows
share|improve this answer

Open the first table and load the values into a dictionary, loop through the second table, deleting records that match in the dictionary.

share|improve this answer
Thanks Hairy. Could you also give me a hint on how to create a dictionary and load values into it? I am still learning python and I have no idea how dictionary works in python. – Sam Sep 19 '11 at 13:13
I should have said list, as a dictionary is effectively a pair of data e.g. an id and name, etc. I meant a list, which is, effectively, an array. So add the data to the list and loop trough it. Lists are mylist = [1,2,3,...] or you can simply instantiate them mylist = [] and then append to them mylist.append(mydata). Then loop through them using the data 'for mydata in mylist:' etc. Here s link from python – Hairy Sep 19 '11 at 13:51
if you want code, then let me know and i'll edit my answer. – Hairy Sep 19 '11 at 13:52
Thanks for the quick response. I looked at the link and I am wondering how do I get all the values of field in the list. So, yes, a code would be very helpful. Thank you again. – Sam Sep 19 '11 at 15:35
If you want to load the fields into the list, simply write a searchcursor, loop through it and append the id to the list. – Hairy Sep 20 '11 at 6:43

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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