SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[11.1.115 Pro] - ANOMALY: UPDATE formatting

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[11.1.115 Pro] - ANOMALY: UPDATE formatting
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 1741

Post [11.1.115 Pro] - ANOMALY: UPDATE formatting Reply with quote
I've got this formatting rule for UPDATE statements below:

Code:

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


Given is an UPDATE statement:

Code:

UPDATE
`obu_cart_item` AS oci
INNER JOIN
`obu_group_of_sales_package` AS ogosp
ON ogosp.`group_of_sales_package_tid` = oci.`group_of_sales_package_tid`
SET `valid_from` = @a
    ,`valid_to` = @b
;


It gets formatted like this:

Code:

UPDATE `obu_cart_item` AS oci
       INNER
JOIN `obu_group_of_sales_package` AS ogosp ON ogosp.`group_of_sales_package_tid` = oci.`group_of_sales_package_tid`
SET `valid_from` = @a
    ,`valid_to` = @b;


Now, if I don't want this to auto-ruin my code, I have to add the INNER keyword to the UPDATE rule, and that will obviously make it no longer work for other types of joins (CROSS, LEFT/RIGHT OUTER). On the other hand, my formatting rule for SELECT statements similarly uses the lone keyword JOIN as an anchor and still does recognize INNER/etc as something that should go hand in hand with JOIN.
Tue Dec 17, 2019 6:39 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7141

Post Reply with quote
Maybe you can break the long UPDATE rule into multiple smaller rules and let the engine pickup the correct JOIN formatting based on the type of JOIN?

1. UPDATE UPDATE ... AS ...
2. CROSS JOIN ... ON ... = ... AND ... OR ...
3. LEFT JOIN ... ON ... = ... AND ... OR ...
4. RIGHT JOIN ... ON ... = ... AND ... OR ...
5. OUTER JOIN ... ON ... = ... AND ... OR ...
6. FULL JOIN ... ON ... = ... AND ... OR ...
7. SET ... = ...
,...
WHERE ... = ... AND ... OR ...
ORDER BY ...
LIMIT ...;


That may solve the UPDATE formatting, but you may need to review and modify all other rules because the new bunch of JOIN rules clashing with your other rules.
Wed Dec 18, 2019 1:30 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1741

Post Reply with quote
This is definitely a good idea, as it would offer greater control over what and how to format. However, since I use multiple RDBMSs and have multiple formatting rules for each, I always refrain from doing that due to the lack of easy migration/synchronization of the changes made to rules.

Would you consider my feature request posted here? I ask there for formatting rules, but that might come in handy for snippets too.
Thu Dec 19, 2019 12:33 pm 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.