Use Jooq To Do A Delete Specifying Multiple Columns In A "not In" Clause

I want to bring a postgres database table in sync with a list of Jooq Records. I have a table with a composite primary key and three other values in each row table(k1, k2, v1, v2,

Solution 1:

Your query can be translated to the following jOOQ code:

// Assuming this:importstatic org.jooq.impl.DSL.*;

using(configuration)
   .deleteFrom(MY_TABLE)
   .where(row(MY_TABLE.FIRST, MY_TABLE.LAST).notIn(
        row("Joe", "Smith"),
        row("Mark", "Taylor")
   ))
   .execute();

This is using DSL.row() to construct row value expressions. Note that ROW is an optional keyword in PostgreSQL. You just happened to omit it in your SQL example.

See also the manual's section about the IN predicate for degrees > 1:

http://www.jooq.org/doc/latest/manual/sql-building/conditional-expressions/in-predicate-degree-n

Solution 2:

It is easier to just delete on a primary key and a good practice as you said. As long as there are no two people called pete jones, something like this should work:

dsl.deleteFrom(MY_TABLE)
 .where(MY_TABLE.first.eq("pete").and(MY_TABLE.last.eq("jones")).execute();