Interbase SQL

[Henry Olive 5]

I wish everyone a healthy day

UPDATE TABLE1 T1 SET
    T1.FIELD1=(SELECT SUM(T2.FIELD1) FROM TABLE2 T2 WHERE T1.ID=T2.ID),
    T1.FIELD2=(SELECT SUM(T2.FIELD2) FROM TABLE2 T2 WHERE T1.ID=T2.ID)
WHERE  EXISTS (SELECT 1 FROM TABLE1 T1 WHERE T1.ID= T2.ID);

This query works w/o any error but doesnt change the values, what is wrong ?

Thank You

 

[Lars Fosdal 1792]

Where do you get T2 from in the outer EXISTS?

[Henry Olive 5]

Oops, Thank you so much Lars

[mvanrijnen 123]

Little tip, always try constructions as this, with a simple select statement first.

eg (not corrected, just as example!):

SELECT * 

   FROM TABLE1 T1 
WHERE  EXISTS (SELECT 1 FROM TABLE1 T1 WHERE T1.ID= T2.ID);

 

so you have an idea what records are gonna be hit.

 

[Guest]

The code with update: How can an alias defined in a subselect be used outside?

Why did that not give an SQL exception?

I suspect a "bug" in that version of IB, i.e. missed exception.

Or do you have a table actually named "T2"?

Or anyone with a brain (mine is not turned on) might have an insight.

[mvanrijnen 123]

1 hour ago, Dany Marmur said:

The code with update: How can an alias defined in a subselect be used outside?

Why did that not give an SQL exception?

I suspect a "bug" in that version of IB, i.e. missed exception.

Or do you have a table actually named "T2"?

Or anyone with a brain (mine is not turned on) might have an insight.

I did not try it out myself 🙂 I give it a try tomorrow, but i if there is no T2 table there should be an error/exception somewhere.

(i'l try it out in DBWorkbench, and delphi with firedac)

 

[mvanrijnen 123]

Original query, does gives an error in  DBWorkbench and FireDac (TFDQuery).

 

[Henry Olive 5]

Thank you so much Mvanrijnen, Danny

[mvanrijnen 123]

On 11/11/2021 at 9:22 AM, mvanrijnen said:

Original query, does gives an error in  DBWorkbench and FireDac (TFDQuery).

 

Where did you run your query ? Did you have any output ?

There has to be an error/exception somewhere.