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

• displaying the result of a Stored Procedure
• displaying result-sets for multiple queries in the same window
• displaying the result of a Join or Group-by query

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

• with the cursor over the data grid, use the right mouse menu > Update Row.
• select Edit Data > Edit Mode
• Ctrl+E
• click the Edit Mode icon

AQT will then:

• determine the unique key of your table. This is discussed in the section User-Defined Keys.
• freeze the key columns
• re-arrange some of the options on the window to be appropriate for edit mode

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:

• Right-click a row and select Delete Row
• Ctrl+D
• If you have selected Options > Table Editing > Delete Row with Delete Key, hitting the Delete Key will also delete the row. This will not work if you are in row-edit mode – in that case the Delete key just deletes a character from the cell.

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.

• If you have specified your key manually (or through a user-defined relationship), before running every update / delete AQT will run a Select count(*) query to check that only a single row will be affected by your change. This has a small performance impact, however is a worthwhile safeguard. This check is not done if your table has a unique index, as the database will have ensured that your key is unique.

  If necessary, this checking can be switched off with Options > Table Editing > Check row is unique before updating / deleting, however it recommended that you leave this option active.

• AQT has Safe Update Mode to check that only a single row is updated or deleted.
• You can run your changes under Transaction mode by selecting Transaction > Begin Trans. This gives you the ability to do a number of changes, then select Commit once you are satisified that your changes have been done successfully. Select Rollback to undo the changes.

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:

• the data you paste must have rows delimited with line-feed and columns delimited with Tab. This is the format of cells copied from Excel; when you copy from a grid in AQT you have the option copying in this format.
• if you are pasting more than one row or more than one column, AQT will prompt you with the number of rows/columns you are about to update. You have the option to abort the paste at this point.
• the data will be pasted from the position of the currently-selected cell. It will only paste into the area of existing rows - it will not paste into new rows. If you wish to do this, use the Paste as new rows function (described below).
• as each row of data is pasted into the grid, the row is updated in the database.
• if an update fails, the paste operation will stop. The data in the most recent row will have been changed in the grid but not applied to the database - either correct it or hit F5 to refresh the table display.

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).

• the data you paste must have rows delimited with line-feed and columns delimited with tabs.
• the new rows will not be added to the table immediately, instead they will be added to the grid as pending rows. This gives you the chance to amend these new rows before they are added to the table.
• the pending rows will be shown in green. If you have a two-color display, the two-color mode is switched off (this is needed to show the green rows).
• once the data is ready to be added to the table, select Edit Data > Apply new rows (or right-click > Apply new rows). AQT will add the rows to the table; as each row is successfully added it will be taken out of pending mode. If there is an error adding a row, the apply process will stop. You can correct the row then resume the apply by clicking on Apply new rows again.
• if you decide not to apply the rows, you can remove the pending rows from the grid with Edit Data > Remove new rows.
• you will not be able to close the window, or go out of edit mode, until you either apply or remove the pending rows.