When creating UPDATE-SET-FROM clause in jOOQ it throws Expecption for CTE field

[Nikola Stevanović]

I am trying to convert following PostgreSQL query into jOOQ:

UPDATE book
SET amount = bat.amount
FROM (
    VALUES (2, 136),(5, 75)
) AS bat(book_id, amount)
WHERE book.book_id = bat.book_id;

VALUES inside of FROM-clause are being created from Map<Long, Integer> bookIdsAmountMap parameter and I am trying to perform that this way:

class BookUtilHelper {

    @SuppressWarnings("unchecked")
    static Table<Record2<Long, Integer>> batTmp(DSLContext dsl, Map<Long, Integer> bookIdAmountMapUpdated) {
        Row2<Long,Integer> array[] = new Row2[bookIdAmountMapUpdated.size()];
        int i = 0;
        for (Map.Entry<Long, Integer> pair : bookIdAmountMapUpdated.entrySet()) {
            array[i]=DSL.row(pair.getKey(), pair.getValue());
            i++;
        }
        Table<Record2<Long, Integer>> batTmp = DSL.values(array);
        batTmp.fields("book_id", "amount");         
        return batTmp;
    } 
}

Then, I try to also create fields which can be accessed like in this example

Field<Long> bookIdField = DSL.field(DSL.name("bat", "book_id"), Long.class);
Field<Integer> amountField = DSL.field(DSL.name("bat", "amount"), Integer.class);
Table<Record2<Long, Integer>> batTmp = BookUtilHelper.batTmp(dsl, bookIdAmountMapUpdated);
// ctx variable is of type DSLContext
ctx.update(BOOK).set(BOOK.AMOUNT, amountField).from(batTmp.as("bat")) 
 .where(BOOK.BOOK_ID.eq(bookIdField));

When I try to update book I get following exception:

column bat.book_id does not exist

Any advice on how to solve this issue would be greatly appreciated. :)

[Lukas Eder]

Thanks for your message. I will reply to your question on Stack Overflow:

https://stackoverflow.com/q/62114313/521799

Regards,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/9ef986b9-0d35-42d4-baf2-1884f4ef1d98%40googlegroups.com.