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 ...
>END

Dorin,

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
begin
update BTable set Column1 = ATable.ColumnA, Column2=ATable.ColumnB, <...>
where <some criteria>
....
end

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)
as
begin
update ATable set ColumnB = (select Column1 from BTable where Column99 >= :arg2)
where ColumnA = :arg1;
end

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...

Helen


All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________