You can edit data in the Table Editor, View Editor and SQL Editor. While the Table Editor and View Editor simply display all data, you can display any result in the SQL Editor.
This topic holds information on:
•Sorting, Grouping and Filtering
Editing Data in the SQL Editor
Once the SQL Editor has a result set of 1 or more rows, you can edit data. If the DBMS supports transactions, by default, Database Workbench will start a transaction for you when you start editing data in the grid.
There's an option Toggle Immediate Data Editing, if you turn this option OFF, you will have to click or use the enter key in each cell before you can start modifying values.
You can use Shift+Del to set a cell value to NULL (empty). Use the Esc button on your keyboard to cancel editing and restore it's original value.
Sorting, Grouping and Filtering
A result set is sorted, grouped and filtered by the server if you wrote an SQL statement that includes clauses to do that.
As quick alternative, you can do this in the SQL Editor as well. Do note that all rows for the result set will be cached at the client side, which can require a lot of memory and make operations slow.
In order to enable this functionality, click on the bar just above the grid:
It will ask you to confirm and change into the following:
Local sorting, grouping and filtering enabled
After enabling local sorting, grouping and filtering the bar heightens and the grid changes to include a filter row. The header of each column includes a filter drop down.
Sorting
Clicking on a header will sort the result set by values in that column, clicking it again will sort it in reverse order. A third click will reset sorting.
When you click a second column header, it will reset sorting first unless you use shift, it then will use the first and second column to sort values.
Using ctrl while clicking a second (or third etc) column will remove that column from the list of columns to be sorted on.
Any sorting as previously returned by the server (via an ORDER BY clause) is gone after performing a local sort.
Grouping
Data grouping is done by dragging a header column to the group by, displayed under (1) in the above screenshot. You can group by multiple columns and switch grouping order by dragging the columns around in the group bar.
Result set grid with grouping on the column CONTACT_FIRSTNAME
When data is grouped, the grid will display expand/collapse images on the left side of the rows. Grouping by multiple columns will result in nested expandable sections.
By dragging a column away from the group bar, you are removing the grouping rule.
Filtering
Filtering can be started by entering a value in one of the columns in the filter-row.
Filtering on "He" creates an SQL filter
Once a filter is created, the filter box at the bottom of the grid will appear.
Alternatively, clicking the drop down button next to the column name shows a list of values. Mind you: this list will include all values and on result sets with a large number of rows, this will be slow.
Filtering by using the drop down list
You can use the Customize button in the filter box to create more complex filters with the Filter Builder.
In the below screenshot, you can see an example of a multi-column filter and it's SQL equivalent below in the filter box of the grid.
The Filter Builder and it's SQL filter result in the Filter Box
Data Fetch Warnings
If you're fetching larger amounts of data to the client side, either by enabling local sorting, grouping and filtering, or by going to the last record in a result set, you can get a Data Fetching Warning. This dialog will be triggered by a certain number of rows, or a certain amount of data (excluding BLOB data). The exact triggers for this can be modified in the Preferences.
Data Fetching Warning dialog
The motive behind this dialog, is to avoid severe slowdowns or crashing the client application with an "out of memory"-error by accidentally fetching too many data into the application.
Mind you that enabling local sorting, grouping and filtering takes up to 4 times the amount of memory of the result set itself.