Updating Data

www.altova.com Print this Topic Previous Page Up One Level Next page

Home >  Retrieving and Editing Data >

Updating Data

To update data in the Result window, first select data for editing. This switches the Result window into the Editing mode, which is indicated by the "Editing Mode" message in the status bar.

 

To edit records, double-click the cell you want to edit and proceed like in any spreadsheet application. You can use the context menu to copy, cut, paste, delete, or select all content or to undo your editing action. You can also paste into the Result grid cell ranges copied from another Result grid or from external files such as Microsoft Excel spreadsheets (see also Adding and Copying Rows).

dbs_result_grid

Result grid

The updated data is not immediately committed to the database but only displayed in the Result grid. To actually commit the update to the database, the Result window provides a Commit button in its toolbar.

 

Data cells that have been edited are indicated by a different background color. The color informs you if there are potential problems with the entered data. For example, if the entered text is not suitable for the data type of the column, the background color of the cell turns red.

 

If XML columns are present in the Result window, an additional button for each row appears inside the XML column: ic_change-xml. Clicking this button allows you to load an XML file into this XML cell or to save the XML content of the respective cell as XML file. Furthermore, you can assign an XML schema to the XML cell, provided that the current data source connection supports this feature. In databases where XML columns are currently not supported by DatabaseSpy, this icon changes to ic_save-xml and you can only save the XML content of the respective cell to a file.

 

In the Editing mode, the toolbar shows the following additional commands:

 

dbs_ic_append

Appends a new row. This command is also available in the context menu, when your right-click a cell. See also Adding and Copying Rows.

dbs_ic_delete

Deletes an existing row. This command is also available in the context menu, when your right-click a cell.

dbs_ic_undo

Undoes all changes made to the Result grid (provided that they have not been committed to the database yet). To undo the editing of a particular cell, right-click it, and select Undo Changes for this Cell from the context menu.

dbs_ic_set_default

Sets the currently selected cell(s) to the default database value. This command is also available in the context menu, when your right-click a cell. Note that the command (and its corresponding button) is enabled only if the database column provides a default value.

dbs_ic_set_null

Set the currently selected cell(s) to a NULL value. This command is also available in the context menu, when your right-click a cell. Note that the command (and its corresponding button) is enabled only if the corresponding database column allows nulls.

 

When you finished editing data, click Commit to send the changes to the database. DatabaseSpy automatically creates the required SQL statements and executes them. If the execution fails, you can view the statements in the Output window and check why they have not been executed successfully.

tab_output-invalid-edit

In such a case, the edited cell remains marked modified in the Result window, however if you choose to retrieve data once again, the (incorrect) update will be lost if you confirm the corresponding warning that pops up in a message box.

 

Using SQL

If you prefer to update data by means of SQL statements, DatabaseSpy can generate the required SQL statement with a click of the mouse (see also Generating SQL Statements). You can either update an entire row or only particular columns of a table. The corresponding SQL statement appears in an SQL Editor window where you can enter the new values.

sql_update-script_new

Take a look at the Department table in your Access tutorial database.

tbl_department

Let's assume you want to change the "Art" department to "Music" and the ID from "2" to "3", you would have to edit the SQL statement as follows:

sql_update-script

 

To update a table by means of SQL statements:

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:
Select the respective table you want to update.
Expand the respective table to see its columns and select the column(s) you want to update.
4.Right-click and select Show in new SQL Editor | Update from the context menu or, holding down the right mouse button, drag the selected objects into an open SQL Editor window and choose Update from the popup. An UPDATE statement for all the selected objects is generated in the SQL Editor.
5.Enter the new values after the equals sign of the respective column.
6.Add a WHERE statement to select only a particular row. Note that all rows of the table will be updated with the same values if the WHERE statement is omitted.
7.Click the Execute ic_execute-sql button to execute the statement and to update the data in the database.

© 2019 Altova GmbH