Sonya Blade Sonya Blade - 4 months ago 52
SQL Question

Update one table from another w/o join statement (firebird)

I'd like to update the columns in on table based on the values of another table, I use a slightly old version of Firebird 2.1 so it doesn't have support for the join statement during update execution. In order to eliminate that, based on the instructions given in original firebird faqs http://www.firebirdfaq.org/faq323/
following statment should work, but it misses some of the values and values for that columns a returned as null as shown in the below tabular form of dataset.

For example, Num 21 from Elements table should have 23 as value in its END_I column because it has exactly the same X_I, Y_I and Z_I values with node tables, but above statement returns null .

update elements E set E.END_I = (select n.node_num from nodes N
where (N.XI =E.X_I and N.YI = E.Y_I and N.ZI=E.Z_I) )
where exists (select 1 from nodes N where (N.XI =E.X_I and N.YI = E.Y_I and N.ZI=E.Z_I))

ELEMENTS

Num End_I End_J X_I Y_I Z_I
17 18.000000 19.000000 0.000000 1.500000 18.000000 0.000000 1.500000 21.000000
18 19.000000 20.000000 0.000000 1.500000 21.000000 0.000000 1.500000 24.000000
19 20.000000 21.000000 0.000000 1.500000 24.000000 0.000000 1.500000 27.000000
20 21.000000 22.000000 0.000000 1.500000 27.000000 0.000000 1.500000 30.000000
21 [null] 24.000000 2.400000 0.000000 0.000000 2.400000 0.000000 3.000000
22 [null] 25.000000 2.400000 0.000000 3.000000 2.400000 0.000000 6.000000
23 [null] 26.000000 2.400000 0.000000 6.000000 2.400000 0.000000 9.000000


NODES
Node_Num XI YI ZI
20 0.000000 1.500000 24.000000
21 0.000000 1.500000 27.000000
22 0.000000 1.500000 30.000000
23 2.400000 0.000000 0.000000
24 2.400000 0.000000 3.000000
25 2.400000 0.000000 6.000000

Answer

Update your query as follow

update elements E set E.END_I = (select first 1 n.node_num from nodes N 
where (N.XI =E.X_I and N.YI = E.Y_I and N.ZI=E.Z_I) )
where exists (select 1 from nodes N where (N.XI =E.X_I and N.YI = E.Y_I and N.ZI=E.Z_I))

You should add first 1 because of firebird 2.1 doestn`t know that subquery return only one row.

Comments