Update the records

[ank123]

Hi,

I want to update all the records for discount = 10 in a table. Please let me know the best way to perform this.

Is the following query efficient for this if not then please let me know the reason -
For each customer exclusive-lock:

assign customer.discount = 10.

end.

TIA

 

[rusguy]

It depends on the amount of records in a customer table – if there are lots of them then you need to commit in batches. In your current code, there is a transaction for each iteration of the loop. This is not efficient. Try something like:

[SIZE=3][FONT=Times New Roman]def var i as integer no-undo.[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]find first customer no-lock no-error.[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]do while avail customer:[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] tran-block:[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] do transaction:[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   do i = 1 to 100: /*set any number here for a batch*/[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     if not avail customer then leave tran-block.[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     run updateCustomer(buffer customer).[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     find next customer no-error.[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   end.[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] end.[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]end.[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]procedure updateCustomer:[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] define parameter buffer b for customer.[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] find current b exclusive no-error.[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] if avail b then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] do:[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   b.discount = 10.[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] end.[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]end procedure.[/FONT][/SIZE]

 

[ank123]

Hi,

Thanks for reply.

In your example, you have used customer buffer NOT customer table directly in procedure updateCustomer. Is there any specific reason for this? What will happen if i directly update customer table instead fo customer buffer?

Please explain this in detail for me.
TIA

 

[tamhas]

The buffer called Customer is merely the *default* buffer for the table. Some of us do almost all or all update using non-default buffers simply because they make it very clear where the record scope is and thus better control over locking and transaction scope.

Here, the main program is reading Customer no-lock, so clearly the record needs to be re-read in order to update it. You should always do this in a different buffer, even if within the same block to avoid confusing the outer loop in terms of position and status.

The second buffer is defined in the internal procedure. Again, you should almost always define such a buffer to limit the scope of the lock and transaction to the internal procedure.

I can't say that I approve of naming this buffer b. No confusion in something this short, but why not bfCustomer or some such so that it is immediately apparent what it is?