Perform multiple SQL Server updates without looping-CodePudding

I have something like this:

var
   text: string ; // data i take from table in my loop.
        
for a = 1 to 100 do
begin
  query.SQL.Text := 'Update dbtable Set atext='   text   ' Where id='   IntToStr(a);
  query.ExecSQL;
end;

Is there a way to avoid calling ExecSQL() 100 times?

CodePudding user response：

In this particular instance, yes, it is easy to do the updates with a single call to ExecSQL().

If the IDs are sequential, you can do this:

var
  text: string ; // data i take from table in my loop.

query.SQL.Text := 'Update dbtable Set atext='   QuotedStr(text)   ' Where id >= 1 and id <= 100';
query.ExecSQL;

If the IDs are not sequential, you could use an in clause instead, but this is less efficient if you have a large list of IDs:

var
  text: string ; // data i take from table in my loop.

query.SQL.Text := 'Update dbtable Set atext='   QuotedStr(text)   ' Where id in [1, 2, ...]';
query.ExecSQL;

Another option would be to create a stored procedure in the DB. Have it take the text and IDs as paramenters, and then perform any needed looping internally. You can then perform a single SQL statement in your code to execute the stored procedure with parameter values.

CodePudding user response：

var
   text: string ;

begin
    query.SQL.Text := 'UPDATE dbtable SET atext='   text   ' WHERE id <= 100';
    query.ExecSQL;
end;

CodePudding user response：

query.SQL.Clear;        
for a = 1 to 100 do
begin
  query.SQL.Add('Update dbtable Set atext='   text   ' Where id='   IntToStr(a));
end;
query.ExecSQL;