Update query not working with PGDac

[piopio1]

Hello,

I have a problem on a Update query. The following is the string I put in FDummyQuery.SQL.Text (FDummyQuery is a TPGQuery)

UPDATE tbl_customer_master_data SET collection_step=collection_step-1 WHERE tbl_customer_master_data.customer_code In ( Select Query1.customer_code From (Select tbl_invoices.customer_code, Sum(tbl_invoices.balance_amount * tbl_currencies.exchange_rate) As ARExposure From tbl_invoices Inner Join tbl_currencies On tbl_invoices.currency = tbl_currencies.id Where tbl_invoices.update_date = Current_Date Group By tbl_invoices.customer_code)Query1 Left Join tbl_customer_master_data On tbl_customer_master_data.customer_code = Query1.customer_code , (Select 1000000 as fixedvalue)Query2 Where Query1.ARExposure>Query2.fixedvalue And (tbl_customer_master_data.collection_category = 758 And tbl_customer_master_data.collection_step = 1) Group By Query1.customer_code, tbl_customer_master_data.collection_category,tbl_customer_master_data.collection_step INTERSECT Select Query1.customer_code From (Select tbl_invoices.customer_code, Sum(tbl_invoices.balance_amount * tbl_currencies.exchange_rate) As AROverdue From tbl_invoices Inner Join tbl_currencies On tbl_invoices.currency = tbl_currencies.id Where tbl_invoices.update_date = Current_Date And tbl_invoices.due_date <= Current_Date Group By tbl_invoices.customer_code)Query1 , (Select tbl_customer_master_data.total_credit_limit As TotalCreditLimit, tbl_customer_master_data.customer_code From tbl_customer_master_data)Query2 Where Query1.AROverdue>Query2.TotalCreditLimit Group By Query1.customer_code)

if I run this SQL statement in PGAdmin III the query works perfectly but if I run the same statement with the following code the fields are not updated:
FDummyQuery.Close;
FDummyQuery.SQL.Clear;
FDummyQuery.SQL.Text:='UPDATE tbl_customer_master_data .....
FDummyQuery.ExecSQL;

I checked FDummyQuery.cachedupdate and it is set to False.

[update]
I have also created the following test:
FDummyQuery2:=TPgQuery.Create(nil);
FDummyQuery2.Connection:=DataModuleDBConnection.PgConnection1;
FDummyQuery2.Close;
FDummyQuery2.SQL.Clear;
FDummyQuery2.SQL.Text:='UPDATE tbl_customer_master_data SET collection_step=5';
FDummyQuery2.ExecSQL;

but not even this one works. Where am I doing wrong in the Query statement ?


Many thanks
Pio Pio

[DemetrionQ]

Hello.

If you make changes inside a transaction, please make sure that you commit the transaction. Please also make sure that your application and PgAdmin work with the same database.

[piopio1]

Hello.

If you make changes inside a transaction, please make sure that you commit the transaction. Please also make sure that your application and PgAdmin work with the same database.

Thank you Dmitry, by committing the transaction I have updated successfully the DB

But what do you mean by "If you make changes inside a transaction" ? I closed the query, cleared SQL.text, filled SQL.Text with the correct text and ran the query. Isn't this one transaction ?

[DemetrionQ]

It is likely that you start a transaction explicitly or implicitly, and then change data. In this case changes are saved in the database only after the transaction is committed. If your application executes only an UPDATE query, it is executed without starting a transaction. Implicit transaction start may be performed, for example, when you call the TPgQuery.Lock method.
You can verify whether there is an active transaction or not using the TPgConnection.InTransaction property.