Hi, I have a table similar to this simplified version.
Test_table
Id integer
description char(15)
quantity integer
sequence_no integer - incremented by insert trigger and generator
Id Description quantity sequence_no
1 Beermat 1 1
2 Beer Mug 3 2
3 Whisky Glass 1 3
How can I insert a particular record - Id 2 another 2 times like this:
Id Description quantity sequence_no
1 Beermat 1 1
2 Beer Mug 1 2
3 Whisky Glass 1 3
2 Beer Mug 1 4
2 Beer Mug 1 5
create or alter procedure test_table_update
as
declare id1 integer;
declare description1 char(10);
declare quantity1 integer;
begin
select Id, description, quantity
from Test_table
where quantity>1
into :Id1, :description1, :quantity1;
while (:quantity1>1) do
begin
insert into Test_table(Id, description, quantity)
values(:Id1, :description1, 1);
quantity1=quantity1-1;
end
update Test_table
set quantity=1
where quantity>1;
end
This works if there is one item with a quantity of more than 1 otherwise
I get "multiple rows in singleton select" error
Id Description quantity sequence_no
1 Beermat 3 1
2 Beer Mug 3 2
3 Whisky Glass 1 3
Any advice gratefully received.
Alan
--
Alan J Davies
Aldis
