Previous Topic

Next Topic

Book Contents

Book Index

Editing table data

You can edit a table directly from the Data Display window. This provides a simple and fast way to update data in a table. If you are doing more complicated changes, or doing mass updates/deletes, you may wish to use the update/delete/insert functions of the Row Display window.

Data cannot be updated in some circumstances - if you are

There are many ways to use Table Edit. You enter Edit Mode by one of the following four methods:

AQT will then:

For Oracle, Informix and SAP-DB, there is an internal Rowid that can be displayed with the table (you can display this column automatically by selecting option Options > Display Options > Show Rowid). If this Rowid is being displayed, AQT will use this, rather than the primary key, as the update key.

Changing rows

To change the value of a table row, double-click a cell (or right-click and select Edit Cell). You will go into row-edit mode. The line pointer (in the row numbers column) will change to a pencil.

Once in row-edit-mode, you can change the value of a cell by typing the new value. Alternatively you can use F11 to enter Extended Edit, which is useful if your column contains a large amount of text. Extended Edit also gives you the Get Values function, which is useful if you are selecting an existing value of the column.

Even though the key columns are frozen, they can still be updated. However some databases do not allow primary key columns to be updated, so this update may fail.

If you change a cell, Esc will undo the change to that cell and take you out of row-edit mode.

You can exit row-edit mode by clicking on the row numbers column.

Once a row has been changed, it will change to green. The change will be applied to the database once you move to the next row in the grid.

AQT does not auto-refresh the data in the grid as you make changes to the table. In some circumstances, therefore, the data in the grid may no longer reflect the data in the table; in this case you should use View > Refresh Data (or F5) to refresh the data.

If an Update/Delete/Insert fails, you will get a message giving the error. For Insert/Update you can correct the data, or cancel the change.

You can cancel the update of a row by hitting Edit Mode > Cancel row update (or F12). The data in the grid row will revert back to its original values.

Deleting rows

You can delete a row by any of the following three methods:

You can delete multiple rows in one operation by selecting the rows then right-click > Delete Row / Ctrl+D / Delete key.

Inserting rows

When in Edit Mode, the grid will display a New Row where you can enter the data for a new table row.

The New Row will be at either the top or bottom of the grid, depending on the setting of Options > Table Editing > New Row Position.

You can copy the data from another row in the grid by right-clicking the new-row and selecting Copy from Another Row.

The row will be inserted once you move to another row (or hit Enter).

You can abort a row-insert by hitting Esc. This will discard all the data for the row. Esc works differently for Insert than it does for Update. When Updating, Esc just aborts the changes to the current cell, whereas when Inserting Esc aborts the changes to the entire row.

The new row will appear in the grid at the bottom (even if your New Row is at the top).

AQT does not auto-refresh the data in the grid as you make changes to the table. In some circumstances, therefore, the data in the grid may no longer reflect the data in the table; in this case you should use View > Refresh Data (or F5) to Refresh the data.

If an Update/Delete/Insert fails, you will get a message giving the error. For Insert/Update you can correct the data, or cancel the change.

If you haven’t entered any data for a column, AQT will not include it as part of the Insert statement. The column will get a value of Null or its default or generated value.

If your table has an auto-generated column (such as an Identity or Serial column), AQT will place (auto) in the column. AQT will not insert data for this column; instead, it will let the database generate the value for this. After inserting an auto-column, you will need to refresh the data (use F5) to see the actual values that the database has generated.

View SQL

If you set Options > Table Editing > Prompt on Update/Delete/Insert you will see the SQL that is being run against the database. This gives you the opportunity to check that SQL is doing what you want, and to cancel it if it does not.

Another method of seeing the SQL code that AQT is running against your database is to switch on Audit Mode. This will record all SQL that is run against your database during Table Editing operations.

Safe Updating

AQT has a number of controls to prevent you accidentally updating or deleting more than one row.

Pasting Data

You can use Paste (Ctrl+V) to paste multiple data values into the grid. This can be used to update multiple rows in your table in a single paste operation:

Paste as new rows

You can use this function to add multiple rows into your table by pasting data from another source, such as Excel or an AQT display. To use this function, click on Edit Data > Paste as new rows (or right-click > Paste as new rows).

Advanced Query Tool
https://www.querytool.com
© 2023 Cardett Associates Ltd. All rights reserved.