SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Q: Formatting ON DUPLICATE KEY UPDATE in MySQL

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Q: Formatting ON DUPLICATE KEY UPDATE in MySQL
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Q: Formatting ON DUPLICATE KEY UPDATE in MySQL Reply with quote
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
Code:

SELECT
    ... AS ...
   ,...
INTO
    ...
   ,...
FROM
    ... AS ...
   ,...
JOIN
    ...
    ON      ... = ...
        AND ...
        OR  ...
WHERE   ... = ...
    AND ...
    OR  ...
GROUP BY
    ...
   ,...
HAVING
    ...
    ,...
ORDER BY
    ...
   ,...
LIMIT ...


and for ON DUPLICATE KEY UPDATE
Code:

ON DUPLICATE KEY UPDATE
    ... = ...
   ,... = ...


formatting the following code
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

Code:

-- /*  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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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 View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
gemisigo wrote:
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 View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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 View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
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


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.