Thank YOU! These help a lot
Examples like these need to be with default FB installation.
Firebird procedure samples as well... is there more FB sample procedures and
triggers
I find that you are writing a book? I pre-ordered one
my Previos posting was sent accidentaly with wrong topic, sorry
-jukka

----- Original Message -----
> <moderator>
> First, please don't hijack threads in this list. New topic, new thread is
> the rule here, as on other technical forums.
> </moderator>
>
> At 08:11 PM 18/03/2004 +0200, you wrote:
> >I have used to write like these, how these must be modified for the
Firebird
> >
> >deleting from one table according to relation with another table
> >------------
> > delete s from tmp_s s,delivery t
> > where s.id=t.id and s.id_part=t.id_part and s.id_row=t.id_row
> >------------
>
> delete from tmp_s s
> where exists (select 1 from delivery t
> where s.id = t.id and s.id_part=t.id_part and s.id_row=t.id_row)
>
>
> >updating fields using relation with another table
> >------------
> > update s set delivWeek=DeliveryWeek,delivYear=DeliveryYear
> > from tmp_s s,order t
> > where s.id=t.id and s.id_part=t.id_part
> >------------
>
> In Firebird you'd need a correlated subquery for each column:
>
> update tmp_s
> set s.delivWeek=
> (select t1.DeliveryWeek from order t1
> where s.id=t1.id and s.id_part=t1.id_part),
> s.delivYear=
> (select t2.DeliveryYear from order t2
> where s.id=t2.id and s.id_part=t2.id_part)
>
> The re-entrant subqueries are pretty ugly - most people would want to do
> this in one hit using a stored procedure if there were a lot of rows to
update.
>
> For a searched update (assuming you have batch restriction like a batch id
> or date range):
>
> create procedure update_deliv_all (
> start_date date, end_date date)
> as
> declare variable v_id integer;
> declare variable v_id_part integer;
> declare variable v_week integer;
> declare variable v_year integer;
> begin
> for select
> id, id_part, DeliveryWeek, DeliveryYear from order
> where batch_date between :start_date and :end_date
> into :v_id, :v_id_part, :v_week, :v_year
> DO
> begin
> update tmp_s
> set delivWeek = :v_week,
> delivYear = :v_year
> where id = :v_id and id_part = :v_id_part;
> end
> end
>
> Or, targeted at a single row:
>
> create procedure update_deliv (
> id integer, id_part integer)
> as
> declare variable v_week integer;
> declare variable v_year integer;
> begin
> select DeliveryWeek, DeliveryYear from order
> where id = :id and id_part = :id_part
> into :v_week, :v_year;
> update tmp_s
> set delivWeek = :v_week,
> delivYear = :v_year
> where id = :id and id_part = :id_part;
> end
>
> However, the single-target approach would more typically be applied in an
> AfterUpdate trigger (or a multi-action trigger if you need to cover
inserts
> and deletes as well) on the order table. The advantages here are No
> Humans; and the second table's update won't happen if the first table's
> update fails for some reason. Also, of course, there is no time-lag for
> synchronising the tables.
>
> create trigger AU_order for order
> active after update
> as
> begin
> update tmp_s
> set delivWeek = new.DeliveryWeek,
> delivYear = new.DeliveryYear
> where id = new.id and id_part = new.id_part;
> end
>
>
> >Could someone post FAQ -address
>
> FAQ for SQL? There isn't one specific to Firebird, though there are
> several for standard SQL on the Web, easy to Google.
>
> Do you have the IB6 Language Reference and the Firebird release notes?
>
> The IB6 manuals, and an SQL reference in HTML format, can be downloaded at
> the Downloads | InterBase page on www.ibphoenix.com
>
> If you are unfamiliar with standard SQL, a book such as Melton and Simon's
> "Understanding the New SQL" could be useful. (It's about SQL-92, so "new"
> is a bit of an anachronism!). Firebird has very few non-standard statement
> syntaxes.
>
> >Basics are easy, now I want to do something...
>
> :-)
>
> /heLen