At 01:47 PM 26/06/2004 +0000, you wrote:

>--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
>wrote:
> > The problem is, it'll be a long job if you have a lot of columns to
>update,
> > since you need a separate subquery and table alias for each column
>you want
> > to update. It really would be more efficient to create entirely
>new
> > records. If you wanted to restore the original PKs, you could just
>add a
> > column to store it, and use the value to update the PKs on the new
>records
> > once you'd deleted the old ones.
>
>Thanks again Helen.
>This looks like the best approach because I do have a lot of columns
>to update.
>Before I follow this approach, are there any possibilties using
>Cursors? I have continued searching & found something on cursor
>statement with for update statement.

What kind of application are you planning to write?

In DSQL (dynamic SQL, that's the language subset used by most modern
programming interfaces, tools, etc.) you can prepare a SELECT...[FOR
UPDATE] statement and then declare a cursor for it. It's not usually
readily available to component interfaces. The FOR UPDATE clause lets you
fetch and operate on one row of a set at a time. "How to do it" (and,
even, "WHETHER you can do it") is very specific to the client programming
environment you're working in.
You can also operate cursors in dynamic applications, via the API.

Cursors (two kinds) are available in PSQL (that's the stored procedure and
trigger language). The more flexible is the FOR SELECT...INTO...DO
construct. It's how most experienced developers would design a
client-independent cursor task for consistency and good network economics.

Cursors are available in ESQL (that's embedded SQL, a.k.a. static SQL)
where you can wrap SQL inside C/C++/Cobol host code and precompile using
gpre).

/heLen