In a SQL result grid you can change cell values and add or delete rows. Your edits do not go to the DB right away: they take effect only after you review the UPDATE, INSERT and DELETE statements NeoSQL generates and run them. When those statements are committed is explained in SQL execution and transactions.
What's on the screen
| No. | Name | Description |
|---|---|---|
| 1 | Changed cell | A cell whose value you changed is colored. |
| 2 | Added row | Green background. Columns with a default value show that default and auto-increment columns show Not Set in grey; if you leave them alone they are left out of the INSERT and the database fills them. The rest start as null. |
| 3 | Row marked for deletion | Red background with strikethrough. It stays in the table until you run the changes. |
| 4 | Row buttons | The Add, Duplicate and Delete buttons. Shown only when the result comes from one table. |
| 5 | Change buttons | The Preview Changes and Discard buttons. Shown only when there are edits. |
| 6 | Change summary | The number of deleted, modified and added rows, such as 1 deleted, 1 modified, 1 added. |
- Hover over the header of a column you cannot edit to see why. A column whose source cannot be identified shows Editing is unavailable because the source table column could not be determined.. Generated columns get a calculator icon in the header and are read-only.
When editing is available
The result grid has no edit switch. A result that meets the conditions below is editable from the start.
NeoSQL analyzes the SQL you ran and identifies, for each result column, which column of which table it came from. Only columns with an identified source can be edited.
| Task | Condition |
|---|---|
| Changing cells | The column's source table is identified, and that table's primary key columns are in the result. Joined results are also updated column by column into each source table with UPDATE |
| Adding and duplicating rows | All source columns of the result come from one and the same table |
| Deleting rows | In addition, that table's primary key columns are in the result |
When you cannot edit:
- Columns built from expressions, functions or aggregates (
count(*),price * qty, and so on) have no source and cannot be edited. Columns from subqueries orWITHcannot be edited either if their source cannot be identified. - Results of SQL not confirmed to leave the data unchanged when run again cannot be edited (SQL re-execution restricted), because the result is queried again after the changes are applied.
- Earlier results cannot be edited after you change the SQL tab's execution target (This result came from a different target, so editing is disabled.).
- Snowflake, Databricks and BigQuery do not enforce primary keys. There you must pick the Row identity columns yourself with the Edit options button in the status bar before you can change or delete rows. Only comparable columns of ordinary types can be picked.
Changing cells
- Double-click a cell, or select it and press
EnterorF2, or just start typing. The cell enters edit mode. - Type the value and press
Enter. The cell changes color and a change summary appears in the status bar. - For long values, edit in the Value panel on the right and click the Apply button.
- In the cell editor and the value panel, choose the kind of value: the Value option (what you type), the Expression option (an expression such as
now()used as is), or the NULL option. Added rows also offer the Not Set option, which leaves the value to the DB default. - Enum columns (
ENUM, PostgreSQL user-defined enums) andSETcolumns are chosen from a list. Spatial data opens an editor for WKT. - Click the eye icon in a foreign key column to see the referenced row in the Referenced Data dialog.
Adding, duplicating and deleting rows
New row: click the Add button. An empty row appears right below the selected row (at the top if none is selected), and its first cell enters edit mode.
Duplicate: select rows and click the Duplicate button, or right-click and choose the Duplicate Row item. Copies appear below the selected rows. Primary key and generated columns are left empty as in a new row.
Delete: select rows and click the Delete button, or right-click and choose the Mark for Delete item. The rows are marked for deletion. Doing it again on marked rows removes the mark.
Select several rows together (click while holding
Ctrl,CmdorShift) to duplicate or mark them at once.Deleting an added row that has not been run yet removes it immediately, without a mark.
Even when the result has zero rows, you can insert rows with the Add button.
Previewing and running
- Click the Preview Changes button. The Preview Data Changes dialog shows the SQL to be run and the number of deletes, updates and inserts.
- Turn the Include schema name option on or off as needed. It decides whether the SQL writes
schema.table. - Check the SQL and click the Execute button. To take the SQL without running it, click the Copy SQL button.
- The statements run one by one, deletes first, then updates, then inserts, in this SQL tab's session. If a statement fails, choose the Stop, Retry or Skip button in the SQL execution error dialog.
- When all succeed, the original SQL runs again so the result reflects the DB. If any statement failed, the result tab shows the executed statements and the error.
- Changes that cannot be generated (such as an update to a table whose primary key is not in the result) are listed with the reason at the top of the dialog, and those statements are left out.
- If one statement changes several rows, the warning An edit statement affected multiple rows. Maximum affected rows per statement: 2. appears. This happens when the chosen key columns have the same values in several rows.
Undoing and transactions
| When | How to undo |
|---|---|
| Before running | Click the Discard button to drop all edits and return to the fetched values |
| After running, in a tab with the Auto Commit switch off | The changes are inside a transaction. Undo them with the Rollback button in the toolbar; they are committed only when you click the Commit button |
| After running, in a tab with the Auto Commit switch on | Each statement is committed immediately and cannot be undone |
- If you run SQL again or change the SQL tab's execution target while you have edits, the Pending Changes dialog asks whether to discard them. The Load More button also asks first.
- If you close the SQL tab while you have edits, you are asked whether to apply or discard them. If you choose to apply, they run without the preview dialog, but the connection profile's protection mode still applies.
Things to keep in mind
- Changes from the result grid go through the connection profile's protection mode. On a connection whose protection mode is read-only, they are blocked and do not run. There is no dangerous SQL confirmation, so on a production DB, always check the SQL in the preview (Safeguards for production databases).
- Updates and deletes find rows by the primary key values in the result. Tables whose primary key is not in the result cannot be edited, so include the primary key columns in your SQL.
- When you edit a table or view without a primary key in the data tab of a table or view, rows are found by the values of all columns. Other rows with the same values may change too, so before applying you are asked with There is no primary key, so the rows to change are found by the values of all columns (orders_log). Other rows with the same values may change too.. If the protection mode needs a confirmation, it is asked in the same window.
- NeoSQL does not know if someone else changed the same row after you fetched the result. When you run your changes, the columns you edited overwrite their values.
- Long text types such as
CLOBarrive in the result with only their first 1,000 characters. When you edit a cell in a column whose values were cut, the editor starts empty, and closing it without typing a new value changes nothing. The Value panel cannot edit that column. This keeps a cut value from being saved and losing everything after it. - When you delete rows, dependent rows follow the foreign key's
ON DELETEsetting. If they must be deleted too, delete them with separateDELETEstatements. - In an account project, editing a cell of a masked column starts the editor empty. If you close it without typing a new value, nothing changes. Masked columns cannot be chosen as the Row identity columns.
Related screens
- Result grid: result tabs, filters, the value panel, and copying.
- SQL execution and transactions: the Auto Commit switch and when changes are committed.
- Safeguards for production databases: where protection mode and the dangerous SQL confirmation apply.
- Table tab: the Data tab that queries and edits the data of one table.