 |
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
gemisigo
Joined: 11 Mar 2010 Posts: 2165
|
|
Q: Formatting ON DUPLICATE KEY UPDATE in MySQL |
|
How should I define rules for INSERT INTO statements (both with VALUES and SELECT FROM) so that the ON DUPLICATE KEY UPDATE part of the code does not get formatted, well, ugly?
By using rule for SELECT
 |
 |
SELECT
... AS ...
,...
INTO
...
,...
FROM
... AS ...
,...
JOIN
...
ON ... = ...
AND ...
OR ...
WHERE ... = ...
AND ...
OR ...
GROUP BY
...
,...
HAVING
...
,...
ORDER BY
...
,...
LIMIT ...
|
and for ON DUPLICATE KEY UPDATE
 |
 |
ON DUPLICATE KEY UPDATE
... = ...
,... = ...
|
formatting the following code
 |
 |
INSERT INTO ertekesites.valasztott_viszonylatok
(
id_vonalvezetes
,id_jarat
,viszonylat_sorrend
,sorrend_tol
,sorrend_ig
,tipus_tol
,tipus_ig
,sorszam_tol
,sorszam_ig
,varos_kod
,dijszabas
,felszallo_nev
,leszallo_nev
,id_kocsiallas_indulo
,id_kocsiallas_erkezo
)
SELECT
lv_id_vonalvezetes
,a_id_jarat
,CASE
WHEN lv_max_viszonylat_sorrend IS NULL THEN 1
ELSE lv_max_viszonylat_sorrend + 1
END
,COALESCE(a_sorrend_tol ,lv_korabbi_sorrend_tol ,lv_min_sorrend) AS uj_sorrend_tol
,CASE
WHEN COALESCE(a_sorrend_tol ,lv_korabbi_sorrend_tol) <COALESCE>=
COALESCE(a_sorrend_ig ,lv_korabbi_sorrend_ig ,lv_max_sorrend) THEN lv_max_sorrend
ELSE NULL
END AS uj_sorrend_ig
,lv_tipus_tol
,lv_tipus_ig
,lv_sorszam_tol
,lv_sorszam_ig
,COALESCE(lv_varos_helyi ,lv_varos_kod)
,lv_dijszabas
,lv_felszallo_nev
,lv_leszallo_nev
,lv_id_kocsiallas_indulo
,lv_id_kocsiallas_erkezo
FROM
(
SELECT
1 AS dummy
) AS dummy
WHERE 1 = 1
AND COALESCE(a_sorrend_tol ,lv_korabbi_sorrend_tol) <lv_max_sorrend>
lv_min_sorrend
ON DUPLICATE KEY UPDATE
sorrend_tol = COALESCE(a_sorrend_tol ,lv_korabbi_sorrend_tol ,lv_min_sorrend)
,sorrend_ig = CASE
WHEN COALESCE(a_sorrend_tol ,lv_korabbi_sorrend_tol)
<COALESCE>= COALESCE(a_sorrend_ig ,lv_korabbi_sorrend_ig ,lv_max_sorrend) THEN lv_max_sorrend
ELSE NULL
END
,tipus_tol = lv_tipus_tol
,tipus_ig = lv_tipus_ig
,sorszam_tol = lv_sorszam_tol
,sorszam_ig = lv_sorszam_ig
,varos_kod = COALESCE(lv_varos_helyi ,lv_varos_kod)
,dijszabas = lv_dijszabas
,felszallo_nev = lv_felszallo_nev
,leszallo_nev = lv_leszallo_nev
,id_kocsiallas_indulo = lv_id_kocsiallas_indulo
,id_kocsiallas_erkezo = lv_id_kocsiallas_erkezo;
|
ends up as
 |
 |
-- /* Formatted to MySQL default at 2017-08-09 10:23:56*/
INSERT INTO ertekesites.valasztott_viszonylatok
(
id_vonalvezetes
,id_jarat
,viszonylat_sorrend
,sorrend_tol
,sorrend_ig
,tipus_tol
,tipus_ig
,sorszam_tol
,sorszam_ig
,varos_kod
,dijszabas
,felszallo_nev
,leszallo_nev
,id_kocsiallas_indulo
,id_kocsiallas_erkezo
)
SELECT
lv_id_vonalvezetes
,a_id_jarat
,CASE
WHEN lv_max_viszonylat_sorrend IS NULL THEN 1
ELSE lv_max_viszonylat_sorrend + 1
END
,COALESCE(a_sorrend_tol ,lv_korabbi_sorrend_tol ,lv_min_sorrend) AS uj_sorrend_tol
,CASE
WHEN COALESCE(a_sorrend_tol ,lv_korabbi_sorrend_tol) <COALESCE>=
COALESCE(a_sorrend_ig ,lv_korabbi_sorrend_ig ,lv_max_sorrend) THEN lv_max_sorrend
ELSE NULL
END AS uj_sorrend_ig
,lv_tipus_tol
,lv_tipus_ig
,lv_sorszam_tol
,lv_sorszam_ig
,COALESCE(lv_varos_helyi ,lv_varos_kod)
,lv_dijszabas
,lv_felszallo_nev
,lv_leszallo_nev
,lv_id_kocsiallas_indulo
,lv_id_kocsiallas_erkezo
FROM
(
SELECT
1 AS dummy
) AS dummy
WHERE 1 = 1
AND COALESCE(a_sorrend_tol ,lv_korabbi_sorrend_tol) <lv_max_sorrend>
lv_min_sorrend
ON DUPLICATE KEY UPDATE
sorrend_tol = COALESCE(a_sorrend_tol ,lv_korabbi_sorrend_tol ,lv_min_sorrend)
,sorrend_ig = CASE
WHEN COALESCE(a_sorrend_tol ,lv_korabbi_sorrend_tol)
<COALESCE>= COALESCE(a_sorrend_ig ,lv_korabbi_sorrend_ig ,lv_max_sorrend) THEN lv_max_sorrend
ELSE NULL
END
,tipus_tol = lv_tipus_tol
,tipus_ig = lv_tipus_ig
,sorszam_tol = lv_sorszam_tol
,sorszam_ig = lv_sorszam_ig
,varos_kod = COALESCE(lv_varos_helyi ,lv_varos_kod)
,dijszabas = lv_dijszabas
,felszallo_nev = lv_felszallo_nev
,leszallo_nev = lv_leszallo_nev
,id_kocsiallas_indulo = lv_id_kocsiallas_indulo
,id_kocsiallas_erkezo = lv_id_kocsiallas_erkezo;
|
I tried merging the two rules by adding the ON DUPLICATE KEY UPDATE rule to various parts of the SELECT rule, alas, to no avail. Hence I guess I should have rules for variants of INSERT INTO instead of trying to force SA into combining the two rules on its own using SELECT and ON DUPLICATE KEY UPDATE. Any hints on how to make that work?
|
|
Wed Aug 09, 2017 4:45 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
I'm afraid you're hitting the limits of the pattern based formatting system. In this case case it might be difficult to achieve the required result in one formatting pass. The ON DUPLICATE KEY may have different actions, and it can appear in both UPDATE and INSERT statements. Moreover, in INSERT it can follow both VALUES and SELECT parts of the INSERT statement which might require adding to SELECT, not to INSERT. I think there is a good chance that a working solution can be found for a specific SQL statement, it would be difficult to create a generic rule working well for all permutations. You may need to format ON DUPLICATE KEY part of the statement separatelly.
|
|
Thu Aug 10, 2017 9:13 am |
|
 |
gemisigo
Joined: 11 Mar 2010 Posts: 2165
|
|
|
|
It seems to. The one thing I don't get yet is why SA fails to apply formatting rules independently of each other. I mean, if there are rules for INSERT (with VALUES removed and having a separate rule for it), SELECT, and ON DUPLICATE KEY UPDATE, why does it scramble the results.
What does SA do with the missing parts of the rule, that is, the ones that are part of the rule but missing from the code to be formatted, such as WHERE, GROUP BY, etc. for the SELECT rules?
|
|
Thu Aug 10, 2017 9:21 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
 |
 |
It seems to. The one thing I don't get yet is why SA fails to apply formatting rules independently of each other. I mean, if there are rules for INSERT (with VALUES removed and having a separate rule for it), SELECT, and ON DUPLICATE KEY UPDATE, why does it scramble the results.
What does SA do with the missing parts of the rule, that is, the ones that are part of the rule but missing from the code to be formatted, such as WHERE, GROUP BY, etc. for the SELECT rules? |
I wish I knew the exact answer to that. I can only guess that keywords referenced in the pattern are used as anchors for code matching, but only the very first keyword is required, all other anchors optional. So it applies the first matching pattern and then recursively applies other matching patterns trying to maintain indenting of the results relative to its parent SQL statement. Have you had ON DUPLICATE KEY UPDATE as a separate statement, for example, if there was a semicolon before it, it would be properly formatted.
|
|
Thu Aug 10, 2017 9:32 am |
|
 |
gemisigo
Joined: 11 Mar 2010 Posts: 2165
|
|
|
|
Hmm, compelling. I wonder why things still got jumbled when I added the ON DUPLICATE KEY UPDATE part to the SELECT rule. Shouldn't it have formatted properly when ODKU was present in the code and simply ignore that part of the rule when ODKU wasn't there?
|
|
Thu Aug 10, 2017 10:04 am |
|
 |
|
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
|
|