Selecting features with field not contained in all records of another field using QGIS?

Question

I am quite new to QGIS 3.4.4 and it is complicated at times to get the proper thinking style.

I want to shift the location of some points in my vector layer. I have a vector layer with points of waterbodies ("waterpoints") with a unique identifier field OBJECTID. I have another vector layer with some points which I relocated on the field. They have a field "Name" which refers to the OBJECTID.

I thought if I can delete all the original points whose "OBJECTID" is contained in "Name" of the other layer and thereafter merge the layers I would be fine. But I can´t make a selection or an export which can check that.

First I didn't manage to get the field from another layer, so I just merged them to run the expression within one layer.

I tried:

OBJECTID IN  (Name)

but it does not work...

I really don´t know how to proceed. I would like to know how to properly write this expression or if there is an alternative method for relocating those points.

Answer 1

You don't mention the QGIS version, but I will assume the newest one. In QGIS 3.6 in Processing you will find Remove duplicates by attribute algorithm. Link to visual change log

Answer 2

If you are using Select by expression, try the following in your waterpoints layer to select features where the "OBJECTID" value is found in the "NAME" field of the other layer. Let's assume your other layer is called newlayer.

get_feature('newlayer','NAME',"objectid") IS NOT NULL

This will check the column NAME in the layer newlayer, using the "OBJECTID" values from your current (=waterpoints) layer.

Please note the name of the other layer and its column are in single quotes and this is case-sensitive.

If it returns a feature (IS NOT NULL) then there is a match and these features will be selected. You can then delete these features.

You can also use the above expression in the field calculator, too, to generate a new column in your waterpoints layer (let's call it dups) which will return 1 if there is a match and 0 if there isn't. And delete all the features where there is a match ("dups" = 1).