<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