I have simple test case, a table with no constraints or primary key.
create table t(num integer, val integer);
insert into t values(1, 23);
insert into t values(1, 23);
insert into t values(1, 23);
insert into t values(2, 23);
insert into t values(2, 23);
When I edit the 1st row in OracleDataTable and modify val column, then after commit, all rows with num = 1 are updated!
I also have this as my query, so the table should be able to uniquely identify a row:
SELECT ROWID, T.* FROM T
OracleDataTable updates rows I did not change
-
- Posts: 24
- Joined: Tue 21 Sep 2010 07:17
We recommend you to create tables with primary keys.
If this is not possible in your scenario, please follow these steps:
1) open OracleDataTable Editor and put the following query in the Select Text tab
"SELECT ROWID, T.* FROM T"
2) navigate to the Command Generator tab and press the Generate Commands button;
3) go to the Update Commands tab, select the Update radio-button, press the Edit button, and replace the existing command text with the following one
UPDATE ALEXSH.T SET NUM = :NUM, VAL = :VAL WHERE ROWID = :Parameter1
4) navigate to the Parameters tab in OracleCommand Editor, choose "Yes" as an answer to "Do you want to regenerate Parameters collection for command?" - you will get three parameters "NUM", "VAL", "Parameter1". You should modify only Parameter1 - select it in the Parameter Name window and assign the following values:
Data Type = RowId
Null Value - clear this checkbox
Source Column = ROWID
Press OK.
5) press the Preview button in OracleDataTable Editor and check the workability of this approach.
We will investigate the possibility to make the same in an easier way.
If this is not possible in your scenario, please follow these steps:
1) open OracleDataTable Editor and put the following query in the Select Text tab
"SELECT ROWID, T.* FROM T"
2) navigate to the Command Generator tab and press the Generate Commands button;
3) go to the Update Commands tab, select the Update radio-button, press the Edit button, and replace the existing command text with the following one
UPDATE ALEXSH.T SET NUM = :NUM, VAL = :VAL WHERE ROWID = :Parameter1
4) navigate to the Parameters tab in OracleCommand Editor, choose "Yes" as an answer to "Do you want to regenerate Parameters collection for command?" - you will get three parameters "NUM", "VAL", "Parameter1". You should modify only Parameter1 - select it in the Parameter Name window and assign the following values:
Data Type = RowId
Null Value - clear this checkbox
Source Column = ROWID
Press OK.
5) press the Preview button in OracleDataTable Editor and check the workability of this approach.
We will investigate the possibility to make the same in an easier way.
-
- Posts: 24
- Joined: Tue 21 Sep 2010 07:17
-
- Posts: 24
- Joined: Tue 21 Sep 2010 07:17