Updating Data

www.altova.com このトピックを印刷 前のページ 1つ上のレベル 次のページ

ホーム >  Retrieving and Editing Data >

Updating Data

結果ウィンドウ内で、To update data  first select data for editing. This switches the 結果ウィンドウ into the Editing mode, which is indicated by the "Editing Mode" message in ステータスバー.

 

To edit records, double-click the cell you want to edit and proceed like in any spreadsheet application. You can use コンテキストメニュー 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 (次も参照してください: 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 結果ウィンドウ provides a Commit ボタン in its ツールバー.

 

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. 例えば、 if the entered text is not suitable for the データ型 of the column, the background color of the cell turns red.

 

結果ウィンドウ内で、If XML 列s are present  an additional ボタン for each row appears inside the XML 列: ic_change-xml. Clicking this ボタン allows you to load an XML file into this XML cell or to save the XML content of the 個々の 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 列s are currently not supported by DatabaseSpy, this アイコン changes to ic_save-xml and you can only save the XML content of the 個々の cell to a file.

 

In the Editing mode, the ツールバー shows the following additional commands:

 

dbs_ic_append

Appends a new row. This command is also available in コンテキストメニュー, when your right-click a cell. 次も参照してください: Adding and Copying Rows.

dbs_ic_delete

Deletes an existing row. This command is also available in コンテキストメニュー, 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 をコンテキストメニューから選択します。

dbs_ic_set_default

Sets the currently selected cell(s) to the default database value. This command is also available in コンテキストメニュー, when your right-click a cell. Note that the command (and its corresponding ボタン) is enabled only if the database column provides a デフォルトの値.

dbs_ic_set_null

Set the currently selected cell(s) to a NULL value. This command is also available in コンテキストメニュー, when your right-click a cell. Note that the command (and its corresponding ボタン) 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 結果ウィンドウ, 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 (次も参照してください: SQL ステートメントの生成). You can either update an entire row or only particular columns of a table. The corresponding SQL statement appears in an SQL エディターウィンドウ 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 個々の data source in the オンラインブラウザー.
2.任意で、 right-click the table you want to update and choose 「データの取得 | 全ての行」 to see the data that is contained in the table.
3.次のいずれかを実行してください:
Select the 個々の table you want to update.
Expand the 個々の table to see its columns and select the column(s) you want to update.
4.右クリック、または、 「新規の SQL エディターで表示 | Update をコンテキストメニューから選択、または、, holding down the right mouse ボタン, drag the selected objects into an open SQL エディターウィンドウ and choose Update from the popup. An UPDATE statement for all the selected objects is generated in the SQL エディター.
5.Enter the new values after the equals sign of the 個々の 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 実行 ic_execute-sql ボタン to execute the statement and to update the data in the database.

(C) 2019 Altova GmbH