Update not updating all records

Update not updating all records

Postby Johnny » Wed 23 Mar 2005 07:16

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][/code]
Johnny
 
Posts: 4
Joined: Wed 23 Mar 2005 07:13
Location: Denmark

Postby Ikar » Wed 23 Mar 2005 10:22

Most probably the problem is in using time in WHERE. What is the type of nr3 and nr4 fields?
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Postby Johnny » Wed 23 Mar 2005 11:44

Hi,

The type of the two variables is

nr3 = Unsigned Integer
nr4 = Unsigned Integer

Regards
Johnny
Johnny
 
Posts: 4
Joined: Wed 23 Mar 2005 07:13
Location: Denmark

Postby Ikar » Thu 24 Mar 2005 09:42

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.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56


Return to MySQL Data Access Components