To delete a row from a table, DatabaseSpy provides the Show in new SQL Editor | Delete data option in the context menu of the Online Browser. Selecting this option generates a DELETE statement in a new or existing SQL Editor window.
Be sure to edit this statement in order to delete the row(s) that are obsolete since executing the statement as is will not delete any rows from the selected table (the default statement selects rows where the primary key is NULL).
To delete data from a table:
|1.||Connect to the database and show the respective data source in the Online Browser.|
|2.||Optionally, right-click the table you want to update and choose Retrieve data | All rows to see the data that is contained in the table.|
|3.||In the Online Browser, right-click the respective 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 popup. A DELETE statement for the selected table is generated in the SQL Editor and a default WHERE statement is added.|
|4.||Edit the WHERE statement accordingly to select only a particular row for deletion.|
|Note:||All rows of the table will be deleted if the WHERE statement is omitted.|
|5.||Click the Execute button to execute the statement and to delete the data from the database.|
Deleting data in the Result window
You have two options to delete records in the Result window: Either set a particular database field to NULL or delete an entire row. Both options are provided in the context menu that is available when the Result window is in the Editing mode. The affected fields or rows are marked modified until the changes are committed to the database. This enables you to also undo the editing if the need should arise.
In the screenshot above, the CONTACTINFO field has been set to "NULL" for the third record, and the entire fifth record has been deleted. Note that the changes have not yet been committed to the database and can still be undone.
To set a database field to NULL:
|1.||Right-click the database field you want to change and select Set Null from the context menu.|
|2.||Click the button.|
To delete a row of records in the result grid:
|1.||Right-click one of the fields in the row you want to delete and select Delete Row from the context menu or click anywhere into the row and click the Delete row button. The row is marked for deletion (see screenshot above).|
|2.||Click the button.|
To restore deleted rows in the database:
|•||Provided that the modified fields in the result grid have not been committed to the database yet, right-click into the deleted row and select Undo changes for this Cell from the context menu. The entire row of records is restored in the result grid.|
© 2019 Altova GmbH