Hi Dominik, Thanks for your nice words. I'm glad to hear that jOOQ helps you be so effective.
You're probably not really running "a simple UPDATE abc WHERE xyz = 123". You're probably asking jOOQ to return a few things that cannot be returned otherwise in SQL Server - unless you know a better syntax? If you're thinking of the OUTPUT clause, that's useful, but won't return all trigger generated data, such as PostgreSQL's UPDATE .. RETURNING. You can turn off this particular syntax by specifying Settings.fetchTriggerValuesAfterSQLServerOutput = false, or you avoid the RETURNING syntax itself. This includes setting Settings.returnAllOnUpdatableRecord = false (which is the default) I hope this helps, Lukas On Wed, Dec 22, 2021 at 8:55 AM '[email protected]' via jOOQ User Group <[email protected]> wrote: > Hi, > at the end of a very fast-pacing year, I end up using JOOQ with SQLServer > for a customer project. Of course, I bought a license in order to enable > Lukas to buy gifts for his beloved ones :-) > But first of all, I've to thank Lukas for those awesome piece of software: > it has made my day-to-day work -and because I'm a freelancer, my whole > life- so much easier than before. I remember with horror back to the days > with ... Hibernate only :-) Since 6 month I'm in the lucky position to do > my coding without JavaScript/Typescript frontend framework, REST API and > JPA. Just Vaadin and JOOQ ... and thats great ! But I digress..... > I'm currious why the UPDATE statement generated by JOOQ are so much more > complicated with SQLServer than with using PostgreSQL ? > A simple UPDATE abc WHERE xyz = 123 becomes to: > DECLARE @RESULT TABLE > ( > [id] INT, > [employee_username] VARCHAR(100), > [leistung_id] INT, > [date] DATE, > [duration] NUMERIC(5, 2), > [created_at] datetime2(3), > [created_by] VARCHAR(100) > ); > UPDATE > [timetrack] > SET > [timetrack].[employee_username] = 'XXX', > [timetrack].[leistung_id] = 9, > [timetrack].[date] = CAST('2021-12-21' AS DATE), > [timetrack].[duration] = 7, > [timetrack].[created_at] = CAST('2021-12-22 08:33:38.43859' AS > datetime2), > [timetrack].[created_by] = 'XXX' output [inserted].[id], > [inserted].[employee_username], > [inserted].[leistung_id], > [inserted].[date], > [inserted].[duration], > [inserted].[created_at], > [inserted].[created_by] > INTO > @result > WHERE > [timetrack].[id] = 1; > MERGE > INTO > @result [r] > USING > ( > ( SELECT > [timetrack].[id], > [timetrack].[employee_username] [alias_99784968], > [timetrack].[leistung_id] [alias_90450706], > [timetrack].[date] [alias_29558215], > [timetrack].[duration] [alias_56189729], > [timetrack].[created_at] [alias_31341003], > [timetrack].[created_by] [alias_31342119] > FROM > [timetrack])) [s] > ON [r].[id] = [s].[id] > WHEN MATCHED > THEN > UPDATE > SET > [employee_username] = [s].[alias_99784968], > [leistung_id] = [s].[alias_90450706], > [date] = [s].[alias_29558215], > [duration] = [s].[alias_56189729], > [created_at] = [s].[alias_31341003], > [created_by] = [s].[alias_31342119]; > SELECT > [id], > [employee_username], > [leistung_id], > [date], > [duration], > [created_at], > [created_by] > FROM > @result [r]; > > Can you explain those strategy or link me to an explanation ? > > Merry christmas and happy new year 🎄 > > kind regards > Dominik > > -- > 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/fd45a5c8-c0ef-4281-9601-5a41b558b979n%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/fd45a5c8-c0ef-4281-9601-5a41b558b979n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- 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/CAB4ELO7Rw%3DcvN_zXPzvnvdcPes9Ys4_aRi%2Bhh6UgtBjuogb_eQ%40mail.gmail.com.
