Previous Topic

Next Topic

Book Contents

Book Index

Row display and row change

Open the Row Display window from the Data Display window by any of the following methods:

In addition, you can activate this window from the Database Explorer window, by right-clicking a table and selecting Add New Row. This allows you to insert a row into a table without first having to display the table.

The Row Display window has two uses:

Row Display window

Toolbar

Some of these buttons will not be visible or active, depending on what mode (display, update etc) you are in.

You can customize the appearance and the buttons that appear on this toolbar.

Displaying rows

The Row Display window opens with the current table/query row displayed, or the first row if no rows were selected.

Changing rows

Most of the other features of this window are to do with updating / deleting / inserting rows in your table.

To amend your table data, select one of the “update modes”:

Updating a single table row

Get the existing values of a Column

Updating multiple rows (mass update)

Note:

use Where

to specify which rows in your table are to be updated

mandatory

use Upd

to specify which columns in your table are to be updated

mandatory

Delete mode

In Delete mode, one extra column, Where, will be displayed in the grid. Tick cells to specify which of the table columns form the Where clause for the delete.

Use the Preview SQL and Check Number buttons as described in the previous paragraph to check the effect of your proposed deletions before you run the delete by using the Delete button. Check Number is a useful safeguard before running an update that is complex or critical.

Updating / Deleting every row

When in Update or Delete mode, AQT will insist that you have a Where column selected. This is to prevent inadvertent updating / deleting of the entire table.

If you really wish to update / delete every row in the table, this can be done by:

Insert mode

In this mode, one extra column, Include, will be displayed in the grid. Use this column to specify which table columns are to be included in the generated Insert statement. By default all table columns are included, however you might wish to exclude some table columns from this grid if you want them to pick up their default or generated value.

You can include all or no columns in the Insert with Edit > Include All or Include None.

Field view options

For large text fields, F2 or Extended Edit will display the entire value.

View a drop-down list of all existing values of the field by hitting F3 or Get Values. A drop-down arrow will appear on the value-cell. Click on this to get the list of values.

Ext Get Vals will show you the Extended Get Values dialog; this is similar to Get Values but gives you more options about the values retrieved.

F4 or Get Constants will give you a drop-down list of typical constants and literals for the column.

Altered data

If you have changed your column values but want to return to their original values, hit Ctrl+R or click on Row > Reload Current.

After you have updated / deleted / inserted some data, these changes will not be reflected in the Data Display. Refresh reruns the query to repopulate the data in the window.

Copy / Paste

You can copy multiple column values by selecting them and hitting Ctrl+C (you select multiple values by dragging the mouse over them).

You can paste data by clicking on a cell and hitting Ctrl+V.

Auto-generated data

If your table has an auto-generated column then (during Insert mode), AQT will place (auto) in the column. You should not supply data for this column as it will be generated by the database.

Key columns

If your table has a primary key or unique key, these columns will automatically be set as the Where columns during Update and Delete modes.

You can see which are your key columns by clicking on Edit > Show Primary Key (or Ctrl+K). The names of your primary key columns will be shown in blue.

If your table/view doesn't have a key, clicking on Edit > Show Primary Key will allow you define a User-Defined Key.

Internal RowID

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 it as the Where column for Update/Delete modes. This is useful if your table does not have a unique key.

Date, Time and Timestamp columns

LOB columns

AQT makes it easy to load a value of a BLOB/CLOB column from a file. Once you set focus on a LOB column a button Load from File will be displayed. Clicking on this allows you to specify a file to be loaded into the LOB during the Update/Insert.

Special Values

Using values with special meanings

By default, AQT encloses all string values in quotes, except

This works well in almost all situations, however you may need to be able to specify whether a string is enclosed in quotes.

Examples of this are:

To see whether AQT has enclosed a value in quotes, use the Show Quotes option to view the field as formatted. You can then add or remove quotes, as needed.

For example, without Show Quotes set:

value

result: it will be

rather than

USER

loaded as string ‘USER

the user-id

?

interpreted as a parameter marker

being loaded as a ‘?'

Running a Change

Preview SQL

Before running your change you can click on Preview SQL to see the SQL statement that will be run. While in the Preview window:

Check number of rows that will be affected

Before running an Update or Delete you can click on the Check Number button. This will run a query against the database to tell you the number of rows which will be affected by your Update/Delete. This is a useful safeguard before running a complex or critical update.

If you have a large table, this option could take a few seconds.

Sorting the Grid

By default, the grid is not sortable. If you wish to do this, click on Edit > Enable sorting. Once this option has been checked, the columns in the grid can be sorted by clicking on the column headers.

Deleting Duplicate Rows

If your table doesn't have a unique index, it is possible to end up with two (or more) identical rows in the table. Deleting one of the duplicate rows can be difficult to do using SQL, as any Where condition will result in both rows being deleted.

If your database allows you to display internal rowids, then you can use these to delete one of the duplicate rows.

Otherwise, the following procedure can be used: