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.
