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 
<https://stackoverflow.com/a/10267071/6805866> 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. :)

-- 
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 [email protected].
To view this discussion on the web visit 
https://groups.google.com/d/msgid/jooq-user/9ef986b9-0d35-42d4-baf2-1884f4ef1d98%40googlegroups.com.

Reply via email to