--- In firebird-support@yahoogroups.com, "Marcin Bury" <mbury@...>
wrote:

>
> Hello all
>
> I have four tables:
> LOGIT_WLO (id integer, logit_status integer ...)
> LOGIT_ORDER (id integer, logit_wlo_id integer -> references

LOGIT_WLO ...)

> LOGIT_REEL (id integer, reel_code integer, srv char(3),

logit_order_id ->

> references LOGIT_ORDER ...)
> LOGIT_SCR_ITEM (id, reel_code integer, srv char(3) [both fields

reference

> LOGIT_REEL], status ...)
>
> due some computations field 'logit_status' in table LOGIT_WLO

changes its value.

>
> Is it possible to change the 'status' field in LOGIT_SCR_ITEM in

single update

> statement called from after_update trigger of LOGIT_WLO table like :
>
> UPDATE LOGIT_SCR_ITEM SCR
> SET SCR.STATUS = 1
> WHERE ??????
>
> it would be simple when only one field from LOGIT_SCR_ITEM table

references

> LOGIT_REEL table, but what is the condition when there are more

than one field

> like in the example above.
>
> TIA
> Marcin
>

Marcin,

I am struggling on a Fridday afternoon to follow your table
definitions, but here goes. You should rather store reel.ID as a
foreign key in logit_scr, good normalisation techniques make these
queries a lot easier. If this works but is slow, then there are two
things to try. You can convert the sub select to a for select
statement and then do a simple update from inside, or convert it to a
where exists query (although I think in this case the optimiser will
do that anyway).


UPDATE LOGIT_SCR_ITEM SCR
SET SCR.STATUS = 1
WHERE SCR.ID in
(
select scr2.ID
from logit_wlo wlo
join logit_order orde on (wlo.ID = orde.logit_wlo_id)
join logit_reel reel on (orde.ID = reel.logit_order_id)
join logit_scr_item scr2 on (scr2.reel_code = reel.reel_code and
scr2.srv = reel.srv)
where wlo.ID = NEW.ID
);


Adam