--- In firebird-support@yahoogroups.com, "majstoru" <majstoru@y...> wrote:

>
> Hi Adam,
>
> First, thanks for help!
>
> Let me to give you a real primer! I have a table article with some
> records (structure id, name, price) and I have a Document table that
> is updating article price (price calculation) (structure article, qty,
> newprice)!
> Here is sample data:
> Article
> id name price
> 1 name1 10.00
> 2 name2 20.00
> 3 name3 30.00
>
> Document
> article qty newprice
> 1 10 15.00
> 2 10 25.00
> 3 10 35.00
>

I am not comfortable with the duplicated data between document and
article. I can not tell whether you are allowed multiple document
records for each article, but normally you would only store this
information in a single table and use a join to connect the records.

update article a
set a.price = (select sum/max/min/etc(d.newprice) from document d
where a.id = d.documentid)

I am not sure if that it is legal though, if not, then I don't believe
it can be done without selecting all the appropriate information and
performing updates from the client program logic.

An after insert trigger on the document table could be used to update
the article table and this would work automatically.

> And that is data. Now I need SQL that will update all prices from
> Document into the Article table (fex: Article 1, price 10 UPDATED to
> price 15), in Delphi:
> for i := 1 to document.recordcout do
> begin
> if article.locate('id', document.fieldbyname('article').asstring, [
> ]) then
> // update procedure
> end;
> all these functions, if it is possible in one SQL, without SP.
> Why not SP. I have SQL's for some functions in my application and this
> is one small problem which I have to complete all client request!
>

still dont understand.

execute procedure dostuff;

is still client driven. It is just better because it doesnt flood your
network with unnecessary traffic.

Adam