Drools-Equivalent of the "not in" SQL

Question

I have two tables A and B. I want to update column A in table A if it doesn't have a match with column B in table B.

I've written the following, but it doesn't work.

rule "Lookup"

when
  B($colB: colB)
  $a : A($colB not contains colA)

then 
  modify($a){
   setcolA(1000)
 }
end

I've tried using accumulate as well but in vain. Any suggestions are highly appreciated.

Equivalent in SQL is

Update colA set colA =1000 from A where colA not in (Select colB from B)

Answer 1

As I mentioned in a comment, this depends on what data types colA and colB.

But let's say we have something like this:

class Person {
  String name;
}

class Cohort {
  List<String> names;
}

And what you want to do is add the person's name to the list of names in the cohort if the name isn't already present. (This is obviously a contrived example since the Real World solution would be to use a Set, not a List, but this is for illustrative purposes only.)

Now we can write a rule that tests if the name is not present like this:

rule "Add person to cohort"
when
  Person( $name: name )
  $cohort: Cohort( names not contains $name )
then
  // make the modification here
end

Note that in addition to contains/not contains, there's also a memberOf/not memberOf pair of operators. Which one you use depends on whether you're testing that a field is part of a collection, or a collection contains a field.

For completeness's sake, you might use memberOf like this:

rule "At least one person is not in the cohort"
when
  Cohort( $names: names )
  exists( Person( name not memberOf $names ) )
then
  // there exists at least one Person whose name is not in the cohort's name list
end

Further, if your list is hard-coded, we even have in/not in operators.

rule "Shirt color not valid"
when
  Shirt( color not in ("red", "blue", "green"))
then
  // the shirt color is some other color, eg "yellow"
end

rule "Unable to ship to country"
when
  Order( country in ("Canada", "Puerto Rico") )
then
  //
end

---

Of course, the contains version a lot like your example which "doesn't work", which makes me think that your data types are not an object plus a collection. Maybe you're trying to find an intersection of two collections -- that's a different sort of algorithm which you could implement using accumulate and collect.

But for the question as-is, these three sets of operators (contains/not contains ; memberOf/not memberOf ; and in/not in) are the three ways you can figure out if an item is present in a collection.