0

I use Firebird 3.0 and I have 3 tables:

Table1: tbl1_id (PK), f2_id (FK), tbl1_f1

tbl1_f2 is a foreign key to table2

Table2: f2_id (PK), f3_id (FK)

f3_id is a foreign key to table3

Table3: f3_id (PK), tbl3_code

Now I need to set Table1.tbl1_f1 = 1 where Table3.tbl3_code = 'value' so I wrote this SQL:

update table1 set tbl1_f1 = 1 where (tbl1_f1 is null)
and table1.tbl1_id in (

select
    tbl1_id

from table1 
   inner join Table2 on (Table1.f2_id = Table2.f2_id)
   inner join Table3 on (Table2.f3_id = Table3.f3_id)

where (Table3.tbl3_code = 'value')

 )

Is my update SQL correct or there is better way to write it ?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
zac
  • 4,495
  • 15
  • 62
  • 127
  • 1
    Short recap of the duplicate: use [`MERGE`](http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-merge.html) – Mark Rotteveel Jul 12 '16 at 18:42
  • @MarkRotteveel I dont know which table to use as a source, if possible give me an answer using Merge – zac Jul 12 '16 at 19:09
  • what about `update table1 ... where (tbl1_f1 is null) and ( 'value' = ( select Table3.tbl3_code from Table3, Table2 where (Table2.f3_id = Table3.f3_id) and (Table1.f2_id = Table2.f2_id) ) )` ? If this works (that is, inner select always returns singleton, not zero-rows and not many-rows) it might be better or worse than for-select approach of @rstrelba (you would have to check and compare PLANs and statement statistics for both on your real data) – Arioch 'The Jul 13 '16 at 13:18
  • 1
    @Wel For the source of merge you can use a select as well – Mark Rotteveel Jul 14 '16 at 06:12

1 Answers1

1
execute block
as
declare id bigint;
begin
for select tbl1_id
from table1 
   inner join Table2 on (Table1.f2_id = Table2.f2_id)
   inner join Table3 on (Table2.f3_id = Table3.f3_id)
where (Table3.tbl3_code = 'value')
into :id
do update table1 set tbl1_f1 = 1 where (tbl1_f1 is null)
and table1.tbl1_id =:id;

end;
rstrelba
  • 1,838
  • 15
  • 16