Update not updating all records

[Johnny]

Hi

I have a "small" problem in using UPDATE, my customer says - and I can see it in the database, that some time not all the records are updated.

The situations is

By means of a TMyQuery I update some records in one table with values from a second table like below

MyQuery2.sql.Clear;
MyQuery2.sql.add('UPDATE A_Tabel,B_Tabel SET A_Tabel.nr1 = B_Tabel.nr1,A_Tabel.nr2 = B_Tabel.nr2 WHERE');
MyQuery2.sql.add('DATE(now())=A_Tabel.LogDatoTid AND A_Tabel.nr3 = B_Tabel.nr3 AND A_Tabel.nr4 = B_Tabel.nr4;');
MyQuery2.Execute;

MyQuery2.sql.Clear;
MyQuery2.sql.add('UPDATE A_Tabel,B_Tabel SET B_Tabel.nr6 = A_Tabel.nr6,B_Tabel.nr7 = DATE(now()) WHERE');
MyQuery2.sql.add('DATE(now())=A_Tabel.LogDatoTid AND A_Tabel.nr3 = B_Tabel.nr3 AND A_Tabel.nr4 = B_Tabel.nr4;');
MyQuery2.Execute;

I can't reproduce the error at my test setup.

Is it possible the first query return before the executing is finish, maybe there is some property I can check to make sure that the executing is finish ?

Code: Select all

[Ikar]

Most probably the problem is in using time in WHERE. What is the type of nr3 and nr4 fields?

[Johnny]

Hi,

The type of the two variables is

nr3 = Unsigned Integer
nr4 = Unsigned Integer

Regards
Johnny

[Ikar]

In this case the problem is most likely in rounding DateTime fields on comparing. A difference in a fraction of a second between two values ('DATE(now()) and A_Table.LogDatoTid ) causes error of calculating all the expression. More certain reply you can search at MySQL AB forums.