Alexander V.Nevsky wrote:

> Ales, I usually make it following next idea, which you can adopt to
> multy-segment PK modifying Where, and for deleting many duplicate by
> removing Exit and skipping first row, selected column have no meaning:

Thanks for the idea! I had to delete a specific row as only one of the
two rows had the correct values, so I adapted your example as follows
and it worked:

create procedure delete_duplicate
(
id1 integer,
id2 integer,
id3 smallint,
id4 integer
)
as
declare variable k integer;
begin
for select distinction_field
from damaged_table
where id1 = :id1
and id2 = :id2
and id3 = :id3
and id4 = :id4
plan (damaged_table natural)
into :k
as cursor tmpcursor
do
begin
if (k is null) then
delete from damaged_table where current of tmpcursor;
end
end

> Theorethically the same can be done selecting RDB$DB_KEY on client
> side and using it in delete, but personally I have not much experience
> with this, if you are interested in general sense, you can try.

I've used the RDB$DB_KEY a few times in the past and somehow didn't
think of it to help me solve this problem. :) Basically you'd have to
declare a variable of type char(8), then add RDB$DB_KEY to the list of
fields to be returned by select and then use it in the delete statement.
Something like this:

create procedure delete_duplicate
(
id1 integer,
id2 integer,
id3 smallint,
id4 integer
)
as
declare variable k integer;
declare variable key char(8);
begin
for select distinction_field, rdb$db_key
from damaged_table
where id1 = :id1
and id2 = :id2
and id3 = :id3
and id4 = :id4
plan (damaged_table natural)
into :k, :key
do
begin
if (k is null) then
delete from damaged_table where rdb$db_key = :key;
end
end

But I didn't test it.
Thanks for help!

On the other hand, is there a web page explaining cursors in more
detail? This is the first time I've seen them used in a stored procedure
and since the original PDF manuals for Interbase 6 don't mention them at
all in the sections explaining stored procedure and trigger language,
and are otherwise just more-or-less cryptically noted in the Language
Reference, I'd like to learn more about them from other sources, if
possible.

-AlesS