SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 6] - UPDATE
Goto page 1, 2  Next
 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 6] - UPDATE
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post [SA 6] - UPDATE Reply with quote
Wicked '?'-s still haunting in UPDATE!
Thu Oct 13, 2011 9:48 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
There is a difference in behavior when selecting columns immediately after typing UPDATE and after typing SET
In case of column selection after UPDATE only = and comma are added. In case of SET, "= ?," is added as in older versions.

To try that, type UPDATE, then start typing table name, but don't press Enter key. Expand the table level, select columns that you want. You will see that ? is not inserted. Hope this helps.
Thu Oct 13, 2011 10:20 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
I understand it works this way but I don't understand why. I rarely write simple UPDATEs. Selecting a table inserts all the columns followed by '= ?' and puts schema_name.table_name after UPDATE which is also not wanted there when writing a complex UPDATE. Selecting columns one by one after SET does the same. Most of my UPDATEs are composite ones fetching data from multiple tables like in the example below.

Code:

UPDATE   u
SET   u.column1 = st1.column1
   ,u.column2 = st2.column2
   ,u.column3 = some_other_expressions
   ,u.column4 = ?
   .
   .
   .
FROM   
   dbo.table_to_update AS u
   JOIN
   dbo.source_table1 AS st1
      ON  whatever
   JOIN
   dbo.source_table2 AS st2
   .
   .
   .


Selecting column4 from the popup after typing a comma I get 'u.column4 = ?'. It would be much more helpful if it only inserted 'u.column4 ='. Pressing space would get me another popup where I could select/filter source table columns or just keep on typing anything else that would dismiss the popup. The same applies to simple UPDATE. The '?' absolutely makes no sense there because I have to press backspace before I can type the new value or press ctrl+space to get the popup. Tends to be very frustrating after umpteen columns selected.
Thu Oct 13, 2011 6:01 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
I understand. For cases you are coding the existing behavior is not efficient. Perhaps there is a need for more control over that sort of behavior for UPDATE statements. What do you think would help most in this case? what kind of options?
Fri Oct 14, 2011 11:57 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
I really don't know :( I'm not fooling myself into thinking there are many users having the same problem but not not putting '?' after '=' would be a great on its own. What was the idea behind putting it there? Does it have some special meaning I'm not aware of? I'm starting to feel embarrassed...
Fri Oct 14, 2011 12:42 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
I'm not sure what led to use of question marks. I can think of it as a placeholder to indicate where a value needs to be entered. If you come up with a good idea for improving the existing behavior, please let us know.

For anyone else out there reading this topic, if you have ideas for improvements in handling UPDATE statements, or for anything else, please speak out.
Fri Oct 14, 2011 2:26 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
The best I could do was to create a snippet :
Code:

UPDATE   u
SET
   u|
FROM   
   $OBJECT$ AS u

It inserts the table I want to update aliased with 'u'. After that I can either join additional tables or press press '.' and select the first column (the popup is not showing the checkboxes). It does not insert '= ?' for the first one, but the next popup comes with checkboxes and selecting column(s) will result in tailing '= ?'. But at least the query is structured as I'd like it to be.

Also, I noticed that when selecting a table after UPDATE, the columns inserted (with '= ?') have the comma at the end of their line even if the formatting for commas is set to Leading (stacked). Is this intentional or a bug?
Fri Oct 14, 2011 3:07 pm View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 319
Country: United States

Post Reply with quote
SysOp wrote:
I understand. For cases you are coding the existing behavior is not efficient. Perhaps there is a need for more control over that sort of behavior for UPDATE statements. What do you think would help most in this case? what kind of options?

Honestly, = should popup the same options as if you pressed , from the select clause or the values clause from the insert statement. Could be either a table column from the from list or local variable.
Sun Oct 16, 2011 8:13 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
judahr wrote:

Honestly, = should popup the same options as if you pressed , from the select clause or the values clause from the insert statement. Could be either a table column from the from list or local variable.

Pressing '=' is fine, it is already working that way, you get the same options. The problem is putting an unwanted '?' when selecting a column from the popup after pressing ','
Mon Oct 17, 2011 2:48 am View user's profile Send private message
mgottvald



Joined: 30 Jul 2019
Posts: 14
Country: Czech Republic

Post Reply with quote
After 8 years I still have problem with that question mark in UPDATE statement (I am sorry if there is some solution - please let me know). I write UPDATE, then schema, then part of table name and use TAB to complete table name. Then I write SET then part of column name and TAB to finish a column name, and it adds = ?. So my statement looks like "UPDATE dbo.Users SET [Login] = ?". I have to use backspace and then start to write value I would like to set. For me it will be perfect, if it will be possible to set SQL Assist to just add = without that ?, so the statement will looks like "UPDATE dbo.Users SET [Login] = " and I could immediatelly start write the value.

Sorry for resurecting such old thread.
Tue Dec 03, 2019 9:22 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
Hi,

You are likely still using a very old version. The behavior is quite different in at least last two several major versions. Question marks have been replaced with comment based placeholders. As soon as cursor enters or or just gets near the placeholder (after arrow navigation keys or mouse click) the placeholder is automatically highlighted, and if you type anything, it replaces the highlighted text. That doesn't require any extra keystrokes, backspace, etc....



Tue Dec 03, 2019 9:37 am View user's profile Send private message
mgottvald



Joined: 30 Jul 2019
Posts: 14
Country: Czech Republic

Post Reply with quote
I am using the most updated version, so just now I am using 11.1.107. Is it possible that it takes some settings from the preceding versions? In that case, where is it possible to change that behaviour? BTW - In your example you have 4 columns to set so I suppose when you are in process of setting columns you set them by checking these columns from the list. Then you have to click in the update or use cursor keys to go between columns. Most of the time I am creating simple update of changing one column. In this case what will happen? I will write following:
1) UPDATE dbo.Use + TAB => UPDATE dbo.Users
2-my current behavior) UPDATE dbo.Users SET Log + TAB => UPDATE dbo.Users SET [Login] = ?
2-behavior you wrote) UPDATE dbo.Users SET Log + TAB => UPDATE dbo.Users SET [Login] = /*{ login }*/
3) when I continue to write do I just immediately replace that placeholder?

In fact I don't need any placeholder. I want SQL assist to just help me with Table and column names.
Tue Dec 03, 2019 11:00 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
mgottvald wrote:
I I suppose when you are in process of setting columns you set them by checking these columns from the list


Not quite. After typing UPDATE and getting a prompt I start typing table name, I select it and press Enter, and the rest of the code is automatically inserted for me, no matter how many columns are there. If I need just as table name without columns, I use Shift + Enter instead of Enter.


There is an option in DB Options -> SQL Assistant -> [assistance type] -> Auto Complete -> Auto Expand SQL Code that can be used to change the behavior and make it not to add columns automatically, but that's typically less productive as it requires more typing on your part.

If you add each column separately I see it automatically adds ? placeholder. This is a feature and I don't see how it can be turned off.

To apply factory default settings, in the Options dialog, select Import/Export button, then click Load Default Options. To incrementally add your custom settings, use the same method, but instead of Default, select one of the configuration backup files that it creates automatically after each upgrade and change, then choose the custom things you want to merge with the default settings. If you override the entire set of default settings you may loose on many new features added in recent versions.
Wed Dec 04, 2019 12:28 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
I failed to mention that there is actually a workaround for what you want. The result may look like below

Code:
UPDATE `event`
SET
    name = ,
    body = ,
    `definer` =



To try that, type UPDATE then start typing table name. Using Arrow Up/Down keys select the required table, using Arrow Right key expand the table level. Now select columns that you need using Arrow Right and Arrow Down/Up to move through the list and select them, Arrow Left will deselect previously selected columns. You will see that in this case "?' symbols Aren't added to the code. Hope this helps.
Wed Dec 04, 2019 12:36 am View user's profile Send private message
mgottvald



Joined: 30 Jul 2019
Posts: 14
Country: Czech Republic

Post Reply with quote
Thanks for your reply. Unfortunatelly both possibilities you have mentioned are not good for me. With Auto Expand SQL Code enabled it does what you wrote, so it prepare the update for updating all columns. But as I said I generally wrote updates modifiing only one column so in this case I have to delete all unwanted columns. The second options works, but for me it is even slower, because I write with all 10 fingers, so to move on keyboard to cursor keys is slowing me down (and of course I use SQL assist to write SQL code faster :-)). And for me it is faster to write parts of the name and just hit tab/enter instead of choosing right column with cursor keys. For me the best solution will be the base behavior with possibility to choose in options if after writing part of the column name and hitting Tab/Enter it should append leftover of column name and equal mark with question mark or just only equal mark. So maybe proposal for feature in the next version. Until than I have to use my backspace key as it is the fastest way for what I am doing.
Wed Dec 04, 2019 3:13 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
Goto page 1, 2  Next
Page 1 of 2

 
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.