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.
The DELETE statement generated by default does not delete any data because the WHERE condition selects only rows where the primary key is null. This is intentional. Be sure to edit the WHERE condition and include the rows that you need deleted. To delete all the rows from the table, remove the WHERE condition.
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.Do one of the following:
•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 context menu. 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.
5.Click the Execute button to execute the statement and to delete the data from the database.
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.