How to do an update inside my select loop ?

[Karen99]

I have the following code :

with qrySql do
begin
close;
sql.clear;
sql.add('select val1,val2,val3,a.* from a');
open;

while not eof do
begin
//here I do some calculations with the fields
//form table a and then I want to do the following :

val3 := val1 + integervalue;

//thus I want to do a update inside my select loop.
//how can I do that ? is it possible ? without using
//another table or query component ?

next;
end;

end;

 

[Karen99]

Oops, the code must be as follows :

fieldbyname('val3').value := fieldbyname('val1').value + integervalue;

so I want to update the same record that the query component is standing on.

 

[towerbase]

I am not sure that you have provided enough information to give a definite answer but why not:

with qrySql do begin
 close;
 sql.clear;
 sql.add('UPDATE a SET val3=val1+'+IntToStr(integervalue));
 ExecSQL;
end;

This will replace every value of val3 with the sum of val1 and integervalue. Is this what you want? I guess it depends on what calculations you want to do with the fields but you haven't said what they are.

Andrew

 

[Karen99]

Ok, let me explain better :

I have a query in which I do a select and then loop through the records. As I loop through the records I build a Tfilestream, from the results that I get from my query. So I need a select query. But at the same time I need to update the record that I am standing on. I did use a tablecomponent to update inside the loop, but this takes to long. So I need another solution. Is requestlive my answer ? I don't really know how to use requestlive.

with qrySql do
begin
close;
sql.clear;
sql.add('select val1, val2 '+
'from A ');
Open;
First;

while not Eof do
begin

TableA.indexname := '';
TableA.findkey([fieldbyname('val1').Value]);
TableA.Edit;
TableAVal1.Value := fieldbyname('val2').value +
integervalue;
TableA.post;

//a lot of other calculations follows : so I need
//a select statement. Please don't query this
//believe me I must do this in a select statement

next;
end;
end;

 

[hilfy]

Don't use RequestLive. Use a separate TQuery with and Update SQL similar to what TowerBase gave you. I'm assuming here that your "integervalue" is calculated at run time and that the TQuery is named "MyUpdateQuery".

Update A
Set Val2 = Val2 + :InputValue
where Val1 = :Val1

The info after each ':' is a parameter. In the IDE or in the code you need to set the DataType (it looks like you'll need ftInteger) and ParamType (ptInput). In your code, you then put:

MyUpdateQuery.ParamByName('IntputValue').AsInteger := integervalue;
MyUpdateQuery.ParamByName('Val1').AsInteger := qrySql.fieldbyname('val1').Value;
MyUpdateQuery.Execute;

You'll note that I don't use the "With" - there are various opinions about this, but I find it easier to debug when I'm explicit about what I'm working with - nested "with"'s can get really hairy to deal with.

-D

 

[Karen99]

I did do it the way Hilfy suggested, but it takes forever ! I am trying to optimize it. I am using D5 and Sql Server 2000. I have already tried to add indexes to my table, but it does not really work. This same application first ran on Paradox and it worked fine, but now that I am running it on Sql Server it takes forever. Any suggestions how to optimize it ?

 

[LucieLastic]

hi

Try it without using ParamByName but writing the sql out in one go, like Towerbase suggested. In the past, I've noticed significant improvement after all ParamByName lines had been jettisoned.

lou