Altova DatabaseSpy 2024 Professional Edition

When you need to delete data from database tables or columns, you have the following options:

•Delete all or specific records (rows) from a table. This means that the respective record(s) will be removed completely.

•Reset one or multiple database fields to null or to the default value, in one operation. This means that no database records will be deleted; only the selected fields will be changed. For more information about resetting database fields to their null or default value, see Updating Data.

The options to delete database records are available, depending on the case, in the Online Browser, in the Result grid, or in both, as described below.

Deleting records with a DELETE statement

To delete records by generating a DELETE statement:

1.Connect to the database and show the respective data source in the Online Browser.

2.Optionally, right-click the table in the Online Browser and choose Retrieve data | All rows to see the data that is contained in the table.

3.Do one of the following:

•In the Online Browser, right-click the table and select Show in new SQL Editor | Delete data from the context menu, or

•Holding down the right mouse button, drag the table into an open SQL Editor window and choose Delete data from the context menu.

A DELETE statement for the selected table is generated in the SQL Editor and a default WHERE clause is added, for example:

4.If applicable, edit the WHERE condition so as to select only the rows that you want to delete. To delete all rows, remove the WHERE clause.

5.Click the Execute button to execute the statement and to delete the data from the database.

The DELETE statement generated by default is constructed so as to prevent accidental deletion of data. You will almost certainly need to edit the WHERE clause in order to be able to delete rows. If you would like to delete all rows of the table, just remove the WHERE clause from the DELETE statement.

To help you easily filter the rows of interest, the generated WHERE clause includes the name of the primary key with some pre-filled value, assuming that the table has a primary key. For example, the following is a DELETE statement generated from a SQLite table where a primary key id exists:

If the table does not have a primary key, the WHERE condition will include the unified list of all columns of all unique keys. If the table has neither primary nor unique keys, the generated WHERE clause will include all the columns, except for computed columns.

The generated values that appear in the WHERE clause depend on the column's default value, on whether it is nullable, and on the data type of the column. For example, the following is an example of a DELETE statement generated from a SQL Server table where (i) no primary or unique keys exist, (ii) the first column is of integer type, and (iii) the second column is of nvarchar type:

Deleting records visually

To delete records visually, from the Result grid:

1.Right-click the table in the Online Browser and choose Edit Data from the context menu.

2.Right-click one of the fields in the row you want to delete and then select Delete Row from the context menu. Alternatively, click anywhere into the row and then click the Delete row button. To select multiple rows for deletion, click the numeric identifier to the left of the row while holding the Ctrl key pressed, and then click the Delete row button.

At this stage, the selected rows are marked for deletion, and you can still revert the changes. To revert changes, either right-click the row and select Undo Changes for this Cell from the context menu, or click the Undo all changes made to the result grid button.

3.Click Commit.