Subject Re: [firebird-support] Re: using sql to update record individually
Author Helen Borrie
At 07:42 AM 26/07/2003 +0000, you wrote:
>Thanks for your solutions. I greatly appreciate it. You mention that
>sp could be quicker, how come?

Fewer queries on the items table, multiple invoicedetails records updated
per items row.

create procedure fixmeup as
declare variable vitemid integer;
declare variable vunit2 char(10);
declare variable vconversion numeric(11,2);
vitemid := 0;
vunit2 := '';
vconversion := 0.00;
for select itemid, unit2, conversion
from items
into :vitemid, :vunit2, :vconversion do
update invoicedetails
unit2 = :vunit2,
conversion = :vconversion
where itemid = :itemid;
vitemid := 0;
vunit2 := '';
vconversion := 0.00;

> But before that, I just notice that my
>invoicedetails tables has no primary key. So I have no idea how to do
>it in SP. Can you share some of your idea?

Initialisations of variables were just a guess - adjust to suit.
I hope you have an index on invoicedetails.itemid.