Append Tool ignores SQL expression

Question

Hi, I'm using the append tool to update some data. I have entered an sql expression to filter the input data, but the tool completely ignored it and updated all records!

In one case the expression was "NAME <> ' '", in another "SHORTNAME IS NOT NULL". Both resulted in records being overwritten with blanks and nulls, respectively.

I'm also using a field map and a matching field.

Feature classes are in different geodatabases, editor tracking is active on the target feature class. Using AGP 3.6.2

Also: It would be great if there was an update tool, without the insert, or an option within the append tool to disable insert (though the tool name would be misleading in that case). I keep deleting the inserted rows because I only want the update.

FranziskaSumpf · Answer

Interesting idea. But the appended rows are not those I filter out with the sql. What you describe is a different scenario than mine.

There are indeed records missing in the target data, because I ran the Delete Identical GP tool (they were identical except for the FIXED_ID that I had added in an earlier step). So of course I don't want to reinsert those duplicates, but I need to update the other records.

To understand what I am doing: I have point features in about 90 different feature classes, with different schemas, and containing duplicates (i. e. some points from one feature have duplicates in one or several other feature classes). I have to make heads and tails of the data and check them against our own database. In a first step, I merged all feature classes into one, keeping only fields that seemed important, and then I manually reduced the number of fields further (e. g. fields named "BEZ", "Name", Name_", "Bezeichn" etc. all mean the same thing, so I merged them into just one field). I had also added a continuous ID across all feature classes, and a field with the name of the feature class, so I can trace everything back to the original source data. I then ran the Delete Identical GP tool to get rid of duplicates, before checking the points against our own data. While checking, I sometimes find that some data did not come across in the merge. In order to get these information into the merged feature class, I use the Append GP tool, but what I actually need is just the Update. I usually don't need an sql query to filter the source data, but sometimes the source data itself is inconsistent, and half of the features have the relevant information in one field (e.g. "SHORTNAME"), and the other half in another (e.g. "NAME"). I tried to concatenate in the field map, but this did not work (I think it was because SHORTNAME is null when it's not filled in).

Sure, there are multiple workarounds I can use, but the issue remains, that the Append tool just ignores an input parameter. To me, that is a bug, and it should be fixed.

RhettZufelt · Answer

Have you tried it without the matching field option?

Since you say you are deleting the appended rows, I assume they are not initially in the target dataset.
I'm wondering if you SQL  expression is working, but, the matching field option is then 'updating' the filtered out rows since they are 'missing' in the target.

If this is the case, testing without the matching field option would not append the blank/null value rows.

R_

FranziskaSumpf · Answer

My SQL queries are correct. One field has a lot of nulls, on those weren't filtered out. Another field has a lot of blanks, just singe spaces (I used the drop down in the expression builder), and those weren't filtered out either. And this were different runs of the tool, combining both expressions.

The bug does not seem to apply, either. I have used the tool quite a lot recently, always with a field map, and it worked like a charm (except when I need an sql query). I also don't know if the field map makes a difference in my case. The data just happens do be very chaotic with lot of different field names for the same thing.