Devart Forums

for example:
crate table test
(
keyField varhcar2(10) not null,
UniIdxFiled varhcar2(10) ,
memo varchar2(10)
)
keyField is key filed ;
UniIdxFiled is UNIQUE INDEX field,but can null;
memo is a normal filed;


when i execute:

uniQuery1.sql.text:='select * from test';
UniQuery1.open;
Dbgrid1.datasoure.dataset:=UniQuery1;

then:
I update data by DbGrid1;

i Monitor the SQL:
it sometimes is :

Update memo =:memo where UniIdxFiled =:OLD_UniIdxFiled ;

I Think ,it must be:

Update memo =:memo where keyField =:OLD_keyField ;


becuause ,when UniIdxFiled is null;


Update memo =:memo where UniIdxFiled =:OLD_UniIdxFiled ;

will update more data that I won't update ;


So ,I hope the automatic SQL for update or delete 's condition Must be
Key fields;

if the tables has no Key fields , when can not use UniIdxFiled too.

because UniIdxFiled can be null sometimes;
you must use condition by KeyField,or UNIQUE INDEX field with can not null,or all fields that selects;

of cause:
i often write :select a.*,rowid from test ;
this update data is OK.

Hello,

Thank you for the information.
We have reproduced your issue.
We will notify you as soon as we have any results.

Hello

We fixed the bug with selecting Unique Key instead of Primary Key as a Key field for TOraQuery and TSmartQuery. But the issue with selecting Unique Key with fields that can be nullable is not solved, because it takes a lot of time to detect if Unique Key has nullable fields or not. This checking can decrease performance of all queries. If we find a way to detect Unique Key with nullable fields quickly, we will fix this issue.

i think it is not difficult. you can judge the old_key_value is null.
if is null , it can not as where condiction;

Hello,

Now the WHERE section in the UPDATE statement is created only at first update call for the table, so if we checked values and generated the WHERE section for the first updating record correctly, we cannot be sure that the WHERE section will be correct for other records.
If we would recreate WHERE section for each record, it could decrease performance of all queries.
We will search the way to resolve the problem.

i see. this is not a good way now.