How update tables

[Artyom]

Hello. I need help with jooq. I have two tables table1 and table2 with the same columns. I get all entries from table2 in the form Result<R>. Please tell me how do I update the entries in Table1 using the result?

[Lukas Eder]

Hi Artyom,

Thank you very much for your message. What do you mean by "updating the entries in Table1"? Do you want Table1 to contain exactly the same data as Table2?

Also, what database product and version are you using?

Cheers,

Lukas

On Thu, May 21, 2020 at 12:28 PM Artyom <matros...@gmail.com> wrote:

Hello. I need help with jooq. I have two tables table1 and table2 with the same columns. I get all entries from table2 in the form Result<R>. Please tell me how do I update the entries in Table1 using the result?

--
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/fcf1e661-1906-4f2a-8484-5e1a4ea4b369%40googlegroups.com.

[Artyom]

Thanks for your reply. I use Postgresql 12.2. I have two tables: aim_table and sourse table. They contain identical columns, but source_table's values are updated. I need to extract all the source_table values and add them to the aim_table. The source_table values I get this way "val result = context.select().from(source_table).fetch()". I need to add this result to the aim-table in some way. I can't use "context.loadInto(aim_table).loadRecords(result).fieldsFromSource.execute" because the aim_table already contains entries with the same ID. Can I do it using the command context.update(aim_table).set() and not specifying fields and values which I should add in the set? Something like "context.update(aim_table).set(result).execute"

четверг, 21 мая 2020 г., 17:15:46 UTC+3 пользователь Lukas Eder написал:

Hi Artyom,

Thank you very much for your message. What do you mean by "updating the entries in Table1"? Do you want Table1 to contain exactly the same data as Table2?

Also, what database product and version are you using?

Cheers,

Lukas

On Thu, May 21, 2020 at 12:28 PM Artyom <matros...@gmail.com> wrote:

Hello. I need help with jooq. I have two tables table1 and table2 with the same columns. I get all entries from table2 in the form Result<R>. Please tell me how do I update the entries in Table1 using the result?

--
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...@googlegroups.com.

[Lukas Eder]

Why the client round trip? You can just use INSERT .. SELECT:

https://www.jooq.org/doc/3.13/manual/sql-building/sql-statements/insert-statement/insert-select/ 

In order to prevent duplicate IDs, you can use any expression on the source ID, or use the ON CONFLICT clause in PostgreSQL.

If for some reason you must pass by a client, to loader API supports the onDuplicateKeyUpdate() clause, which also translates to ON CONFLICT.

I hope this helps,

Lukas

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/d73cbd28-fb4e-44d6-99f6-795f6eb660e9%40googlegroups.com.