Increment column value when inserting a Record

[Vijay Ganesan]

Have this way of inserting a Record:

Table<Record> table = ...

InsertQuery<Record> insertQuery = context.insertQuery(table);

Record> record = ... create record and set values for columns ....

insertQuery.addRecord(record);

       

Now, what I want is - for a particular integer column in the record, I want to set value to be 'current value + 1' i.e. increment the current column value that exists in the database for that row by 1. How can I do this? Unclear how Field's add() be use in conjunction with this approach of inserting a Record.

[Lukas Eder]

Hello Vijay.

I'm not sure how incrementing a value fits with inserting a record. You probably want to update the record?

http://www.jooq.org/doc/3.3/manual/sql-building/sql-statements/update-statement

You'd then write something along the lines of this:

create.update(TABLE)
      .set(TABLE.VALUE, TABLE.VALUE.add(1))
      .where(TABLE.ID.equal(3));

Cheers

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.
For more options, visit https://groups.google.com/groups/opt_out.

[Vijay Ganesan]

Actually I meant to say update:

Table<Record> table = ...

UpdateQuery<Record> update = this.context.updateQuery(table);

update.setRecord(...some record where I want to say add() for a field...);

update.addConditions(...some condition...);

[Lukas Eder]

Hi Vijay,

Thanks for the clarification. You have several options:

1. Increment the value in your Record

2. Don't use setRecord(), but set values one by one on your UpdateQuery

3. Use setRecord(), and then overwrite the value for your specific field using a new value

UpdateQuery is a mutable representation of your UPDATE statement. It can be changed various times prior to execution...

Hope this helps,

Lukas

[Vijay Ganesan]

Lukas,

Setting values on the UpdateQuery works great. 

But if I wanted to do "1. Increment the value in your Record", how do I do that?

Record record = new SomeRecord();

record.setValue(Tables.SOME_TABLE.SOME_FIELD, Tables.SOME_TABLE.SOME_FIELD.add(1));

clearly won't work.

Thanks

[Lukas Eder]

Hello Vijay,

jOOQ UpdatableRecords are for CRUD. Using Field expressions is not possible in CRUD. What I meant by "1. Increment the value in your Record" was this:

record.setValue(SOME_TABLE.SOME_FIELD, record.getValue(SOME_TABLE.SOME_FIELD) + 1);

I.e. "Increment the value in your Record in Java"