Amending, Exporting & Loading Data

Amending & Editing Table Data

Table of Content

AQT provides a number of ways to amend data, depending on the volume of amendments to be made.

Running an action SQL statement

Within the Run SQL window, you can run action SQL statements as well as queries.

You can run any valid SQL Action statement – Update, Delete, Insert, Create, Drop, Grant etc. Stored Procedures can be run.

When an Action statement is run you will get a message indicating the success (or otherwise) of the statement and the number of rows affected.

You might consider running the statement under Transaction Mode so you can roll back the changes if you decide they haven’t done what you have intended.

You can run multiple statements (separated by a semicolon or other delimiter). See Running multiple action statements for a discussion on this.

Amending individual rows

As well as running action queries, Advanced Query Tool provides two mechanisms for updating/deleting/inserting rows.

Table edit mode

Once you have displayed a table, you can enter Table Edit Mode. In this mode you can amend the table by changing the data in the grid. You can also delete or insert rows.

Row display window

A more comprehensive facility to update data is from within the Row Display window. Use this method if you are doing more complicated changes, such as:

mass updates / deletes
setting a column to a system-constant or the value of another column
loading a LOB value from a file

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

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.

Row display and row change

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

Use the Row menu
Any of the five yellow icons (Row detail, update row, delete row, insert duplicate row, insert new row)
Click in a row then right-click to display the quick menu.

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:

to display the details of a table/query row
to provide an easy way to update your data.

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.

use the arrow icons or the Goto menu-item to move to other rows in your table.
for a particularly large value, click on the row then hit F2 or Edit > Extended Edit to see the data in a larger window.
click on Print for the two print options: Print Screen and Print Data.
resize the rows to display large column values.

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”:

Update mode to update this or other rows in the table.
Delete mode to delete this or other rows in the table.
Insert Dup mode to insert a new row in the table, using the values of the current row as a base.
Insert New mode to insert a new row in the table with the table columns initialised to default values.
These will be the default values of the table columns as defined to the database. If these aren’t available, the default values will be blank, zero or current date/time depending on the data type.
Preview SQL displays the SQL that is being generated. You will see the effect of any Where and Update options that have been set.

Updating a single table row

Tick cell/s in the Where column to indicate the table columns that comprise the unique key. If your table has a primary key or unique key, these columns will be marked for you automatically.
For Oracle and Informix users, if the ROWID is in the result-set, it will be used as the Where column.
Change the data values. As you change a value, the Upd column will be ticked.
Click on the Update button.

Get the existing values of a Column

you can click on Get Values to get the existing values of a column. These will display as a dropdown list of values to select from.
by default, Get Values will get the existing values of the column in the table. However if Options > Row Display/Update > Get Values from Foreign Keys is specified, AQT will see whether there is a foreign key defined for that column. If so, it will get the values of the foreign key column.
clicking on Get Ext Values will show you the Extended Get Values dialog. This provides more options for the Get Values function.
clicking on Get Constants will show you common constant values and expressions for the data type.
once you have displayed a drop-down list using one of these buttons, it will be redisplayed when you next click on the column.
for Date / Time and Timestamp columns, there will also be a button Select Date. This allows you to select a date/time value from a calendar box.

Updating multiple rows (mass update)

Tick cell/s in the Where column to indicate which rows in your table are to be updated
Change the data values. As you change a value, the Upd column will be ticked.
Optionally, click on the Check Number button. AQT will tell you how many rows will be updated by your update. This is a useful safeguard before running a complex or critical update.
The Preview button will display the SQL that AQT will run to perform your update.
Click on the Update button.
If you have Safe Update Mode selected, and you have updated more than one row, you will be prompted for whether this update is to proceed.

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:

selecting some Where column (to keep AQT happy)
click on Preview SQL
remove the Where clause from the SQL
click on Run

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.

if you are edit-mode on the cell (row-pointer on the left is a little pencil), the data will be pasted into the cell.
if you are not in edit mode, the data will be copied into either a single or multiple cells. AQT will examine the clipboard to see whether it holds a single value or multiple values. Multiple values are delimited by either a linefeed or a tab. This means that, when copying data from Excel or AQT, you can copy a set of values either horizontally (in a row) or vertically (in a column), and it will paste successfully into AQT.

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

When you go into one of the update modes, any date / time / timestamp columns will be changed from their display-format to their update-format. If your timestamp column has a zero time-part (which is normally suppressed during the display of the column), you will notice that this has been added to the column. This allows you to update this time part of the column.
For Oracle, you will get an option Add To_Date. This specifies whether you want the TO_DATE function applied to any date values being updated, or the TO_TIMESTAMP function for timestamp values. Click on Show Quotes to see the column value with this function included.
You will get a button Select Date. Clicking on this shows you a date-picker window, which allows you to select a date, timestamp or time value.

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.

Once you have selected a file, you will get <aqtfile>filename in the column value. This is a control keyword to tell AQT to load the column from a file. You can also invoke this function by manually typing <aqtfile>filename (specify a valid filename) into this column.
Click on Preview SQL to see how AQT processes the LOB insert/update. It will replace the <aqtfile>filename with a parameter marker (?), and use an AQT control statement to set the value of this parameter. It is done this way because files can only be loaded into LOB values by using parameters.

Special Values

If you specify a value of ?, AQT will interpret this as a parameter marker and prompt you for the value when you do the update/insert. If you really want a value of ? inserted into your database, use the Show Quotes option to show how AQT will use quotes, then add your own quotes to get the result you want.
If you specify a value of NULL, AQT will set the column to NULL. If you really want a value of NULL inserted into your database, use the Show Quotes option.

Using values with special meanings

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

Null
?
Date/time columns with a non-numeric value

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:

if you want to set your column to a system variable. Examples: SYSDATE, USER.
if you set the column to another column, or a function involving another column.
Examples: Current_Salary/100, Substr(Last_Name,1,1), Units_Ordered – Units_Delivered.
you want to specify a value of ? or NULL.
You want to include trailing blanks with your character value (normally AQT will remove trailing blanks).

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

to run your change, click on the Update / Delete / Insert button.
if you change more than one row, and you have Safe update mode selected, you will be prompted as to whether you want the change to proceed or not. This allows you to back out the change should it affect more rows than you expected.
alternatively, before running your change you can click on Transaction to enter transaction mode. This will enable you back-out your change if you decide that it didn’t do what you want.

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:

you can modify the SQL
click on Run to run the SQL.
click on Copy to SQL Window to copy this SQL to the Run SQL window.
click on Save Query to save the SQL. Retrieve Query retrieves a saved query.
if you change this SQL but wish to return to its original version, click on Refresh SQL.

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:

display one of the duplicate rows
click on the Del button in the toolbar to go into Delete mode.
Click on Delete. You will probably get a message saying You have affected 2 rows. Do you want this to proceed?. Click on Yes. Both the duplicate rows will then be deleted.
now click on the Dup button in the toolbar to go into Load Duplicate Row mode.
click on Insert. This will insert the row back into the table.

Inserting BLOBs or CLOBs

AQT provides an easy way to load a BLOB or CLOB value from a file.

BLOBs (Binary Large Objects) are used to hold data such as images and sound files.
CLOBs (Character Large Objects) are used to hold large amounts of text.

This feature also works for other column types, generically called LOBs:

IMAGE
LONG VARBINARY
TEXT (Sybase, SQL Server)
LONG VARCHAR (DB2),
LONG (Oracle)
LONG RAW (Oracle)
MEMO (Access).

There are two reasons you may wish to load a LOB column from a file:

LOBs are often very large, so are difficult to load via normal SQL
BLOBs often contain binary data, which is also difficult to load using normal SQL

To load a LOB value from a file

There are a number of way of loading a LOB from a file. These all use parameter markers, which is the method AQT uses for supplying the LOB data.

Parameter Marker

In the Run SQL window, use a parameter marker (?) in the Insert/Update statement where the LOB is to go. Example:

Insert into blob_table (keycol, blobcol) values(1,?)

When you run this SQL, you will be shown the Enter Query Parameters window. There will be a button Load from File. Click on this to specify the file you wish to load into the LOB column (the text <aqtfile>filename will appear in the parameter value).

Parameterised Insert

Alternatively, in the Run SQL window, you can supply the parameter value (to avoid being prompted):

--aqt parms,parmno=1,value=<aqtfile>filename

Insert into blob_table (keycol, blobcol) values(1,?)

This method is used internally in AQT for loading LOB data.

Insert / Update Row window

In the Insert/Update Row window, specify a ? in the LOB column value. When you run the insert/update, you will be prompted with the Enter Query Parameters window from which you can specify the filename.

Load from File in Insert/Update window

In the Insert/Update Row window, when you click on a LOB column this will show a button Load from File. Click on this to specify the file to be loaded into the column. This will generate a Parameterised Insert statement - click on Preview SQL to see this.

Enter Query Parameters widow

This window prompts you for parameters needed for a query. This will show a Load from File button for parameters of types BLOB, CLOB, IMAGE and CHARACTER/TEXT greater than 1000 bytes.

You will not see this button in Oracle (as Oracle does not give the parameter type). Even though this button is not present you can still load the column from a file. You do this by typing <aqtfile>filename into the parameter value.

Data Loader

The Data Loader can be used to copy data between tables (even if they are on different database). This supports loading LOB data; and can therefore be used for moving LOB data between tables.

Export as Inserts

The Export Data function can export data from a table as a series of insert statements. This function supports LOBs; when data is exported, the LOBs are written as files, and the insert statements formatted as parameterised Insert statements (as described earlier). This script can then be used to load the data (and LOBs) into another table.

LOBs and DB2/UDB

When this feature is used with VARCHAR and LONG VARCHAR columns it sometimes gets a “truncation error” message. The reason for this are not yet understood.

Loading Zero-Length LOBs

Both Oracle and DB2 cannot load zero-length LOBs. The ODBC Driver hangs or crashes when this is attempted. To circumvent this, for these databases AQT will load a single blank into the LOB .

LOBs and Oracle

In order to view and load LOB columns, you must

to load BLOBs correctly, you must disable Unicode support. Because Oracle incorrectly reports BLOBs as being character (not binary) they will not load correctly when Unicode is enabled.
use the Oracle ODBC Driver. The Microsoft ODBC for Oracle driver does not support LOB columns; when you try to display LOB values you will get the message "Statement has failed, however your database does not return any errors".
when you configure the Oracle ODBC Driver you must check the Enable LOBs option (this is in the Oracle tab at the bottom). If you don't do this you will be unable to insert LOB values, instead you will get the message: "ORA-01461: can bind a LONG value only for insert into a LONG column"
loading LOB data from Files doesn't appear to work for LONG columns. This may be a limitation of the Oracle ODBC Driver.

LOBs and Sybase

AQT has trouble loading LOBs into Sybase. The reason for this is that Sybase doesn't return correct information on the data type of parameters. This means that when you run a query such as:

Insert into blob_table (keycol, blobcol) values(1,?)

AQT doesn't know data type of the ?, so can't pass the data to it correctly. The solution is to manually tell AQT the data type; this is done as follows:

--aqt parms,parmno=1,value=<aqtfile>filename,dtype=image

Insert into blob_table (keycol, blobcol) values(1,?)

Here the data type of the parameter is specified with the dtype parameter.

This is done automatically when you insert a LOB using the Insert / Update Row window, however you must manually code the dtype when you run this statement in the Run SQL window.

Problems updating data

If you are getting errors when you try and update or delete data, you can try switching off Safe Update Mode.

When you are using Safe Update Mode, AQT runs the updates within a transaction. This allows you reverse the change of you update / delete more rows than intended. Sometimes there are problems with running an update within a transaction:

MS Access can give you error Option not valid at this time
DB2/400 can give you error SQL7008 - (table) in (file) not valid for the operation. This will happen if your table is not being journalled.

The solution to both of these is to switch off Safe Update Mode.