Use JOOQ to do a delete specifying multiple columns in a "not in" clause

Question

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, v3) ``` For example, the data might be ``` Last, First, Age, Weight, Height Smith, Joe, 21, 75, 160 Jones, Pete, 23, 80, 180 ``` (excuse the poor form of using names as primary keys....) I also have a list of Jooq Records for that table in my java code. Let's say that there's two java records ``` [ , ] ``` What I would want to happen is when I run some code, - The database row for Joe Smith has it's height updated - A new row is inserted for Mark Taylor - The database row for Pete Jones is deleted I have managed to create a function that does the first two parts, but am stuck on the third. I was hoping to have a pretty simple "one liner" in JOOQ that basically did ``` delete from my_table where (first, last) not in (values ('Joe', 'Smith'), ('Mark', 'T…

Accepted Answer

Your query can be translated to the following jOOQ code:

```
// Assuming this:
import static 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

Use JOOQ to do a delete specifying multiple columns in a "not in" clause

Solution

Problem