Append Tool ignores SQL expression

[FranziskaSumpf]

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.

[Robert_LeClair]

Your filter may not be excluding what you think (spaces vs NULLs).  SHORTNAME IS NOT NULL only excludes NULL values.  It will not exclude empty spaces or strings that are just spaces.  NAME <> ' ' excludes exactly one space and will still allow an empty string, two spaces, etc.  So your source features can still pass the filter and then append blank/NULL values into the target, overwriting existing values.  Try filtering with something that excludes both NULL and "blank-ish" strings.  For example:

SHORTNAME IS NOT NULL AND TRIM(SHORTNAME) <> '' or NAME IS NOT NULL AND TRIM (NAME) <> ''

There is a BUG-000157944 for ArcGIS Pro that was fixed at 3.7 that may be related as well.

[FranziskaSumpf]

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.

[RhettZufelt]

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_

[KevinSaavedra]

I'm able to reproduce this with a simple SQL Query (is not null) and matching field for update. As a potential workaround, can you try Select Layer By Attribute on the input data and use that selection instead of the SQL query in the Append tool?