Jump to content
Henry Olive

Interbase SQL

Recommended Posts

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



 

Share this post


Link to post

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.

 

Share this post


Link to post

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.

Share this post


Link to post
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)

 

Share this post


Link to post
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.

 

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×