Subject Re: [ib-support] Update in SP
Author Helen Borrie
At 11:17 AM 09-05-01 +0300, you wrote:
>Hi everyone !
>How can I update a table in a SP using a FOR SELECT ... clause ?
>FOR SELECT <some columns> from <table> into <vars> DO BEGIN
> ... Here is the updating part ...

In a FOR SELECT loop on one table you can use the selected data to update another table but not to update the one which is the subject of the loop, e.g.

for select ColumnA, ColumnB, ColumnC
from ATable
where <some criteria> do
update BTable set Column1 = ATable.ColumnA, Column2=ATable.ColumnB, <...>
where <some criteria>

You can however write a SP that takes one or more arguments and uses the arguments to form an expression for use in an UPDATE statement to be applied to a "selected" set of rows, using a regular SQL statement, for example:

create procedure do_update(arg1 integer arg2 date)
update ATable set ColumnB = (select Column1 from BTable where Column99 >= :arg2)
where ColumnA = :arg1;

Often (as in this trivial example) you can achieve the same thing with a dynamic sql statement and wouldn't require a SP for it...


All for Open and Open for All
InterBase Developer Initiative ยท