SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Formatting for MySQL UPDATE statement

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Formatting for MySQL UPDATE statement
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Formatting for MySQL UPDATE statement Reply with quote
The default formatting rule for UPDATE statements in MySQL is:

Code:

UPDATE
   ...
SET
   ... = ...,
   ...
WHERE
   ... = ...
   AND ...
   OR  ...
ORDER BY
   ...,
   ...
LIMIT ...;


That rule leaves the following code with joined tables:

Code:

UPDATE 
    verseny.palyaelem_adatok AS u  INNER JOIN tavolsagok AS t ON  t.ronk = u.ronk
SET u.tavolsag_sum = t.tavolsag_sum
WHERE   1 = 1;

mostly as
Code:

UPDATE
    verseny.palyaelem_adatok AS u INNER JOIN tavolsagok AS t ON t.ronk = u.ronk
SET
    u.tavolsag_sum = t.tavolsag_sum
WHERE
    1 = 1;


I tried to merge it with the my SELECT rule of:

Code:

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


and ended up with this:

Code:

UPDATE   
   ... AS ...
   ,...
   JOIN
   ...
      ON  ... = ...
      AND   ...
      OR   ...
SET   ... = ...
WHERE   ... = ...
   AND   ...
   OR   ...   
ORDER BY
   ...
LIMIT ...;


But this formats the above code as:

Code:

UPDATE 
    verseny.palyaelem_adatok AS u INNER
    JOIN
    tavolsagok AS t
        ON  t.ronk = u.ronk
SET u.tavolsag_sum = t.tavolsag_sum
WHERE   1 = 1;


leaving the INNER keyword on a wrong line, which obviously was not my intention. The same fragment of rule (the part affecting JOINs) works as expected, that is, moving INNER/LEFT OUTER/RIGHT OUTER/etc together with the JOIN keyword. Any ideas about what's missing?
Sun Jul 16, 2017 6:17 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
Do you have a separate formatting rule for JOIN. If yes, it might be interfering.
Sun Jul 16, 2017 12:47 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
No. The only other rule having the word JOIN in it is for SELECT. Could that be interfering? Not that it's something I could afford to have disabled or missing, to be honest.
Sun Jul 16, 2017 12:53 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
It shouldn't, the first keyboard is different.
What do you get if you add additional ... in front of JOIN?
Sun Jul 16, 2017 12:56 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
It got worse:
Code:

UPDATE 
    verseny.palyaelem_adatok AS u INNER JOIN
    ...
 tavolsagok AS t
        ON  t.ronk = u.ronk
SET u.tavolsag_sum = t.tavolsag_sum
WHERE   1 = 1;

Sun Jul 16, 2017 12:58 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
I tried brute force method and added a separate rule for INNER JOIN to place it on a new line
Code:
...
INNER JOIN
...



And conceptually it does kind of what you want, except that it creates wrong indent for the INNER JOIN. I feel like it's reaching the limits of what the code formatting can do using the existing rules processing system without internal programmatic changes.
Mon Jul 17, 2017 7:50 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
I guess I'll just live with one thing malformed and that will be UPDATE statements. I don't have many of those. Thanks for figuring this out.
Mon Jul 17, 2017 10:06 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.