Devart Forums

If I update a multi table query why does the where clause include every field and not just the ones required to identify the record.

e.g. Tbl1 has fields A, B, C & D with primary index on A.
Tbl2 has fields A, E, F, G with primary index on A.

Query1 has SQL

select * from Tbl1 inner join Tbl2 using (A);

If I had the following code

Query1->Edit();
Query1->FieldValues["B"]=6;
Query1->Post();

then the SQL generated by MyDAC and shown in DBMonitor would be

Update Tbl1 set B=6 where A=? and B=? and C=? and D=?;

instead of

Update Tbl1 set B=6 where A=?.

I'm forever getting these "Update failed - 0 records found" messages when trying to Edit multi-table queries and I suspect it's not finding the record because of the non-primary comparisons.

Try to generate update SQL statements manually using the TMyQuery.SQLUpdate property.
To create a SQLUpdate statement at design-time, you can use the query statement editor.

A couple of questions on that Dimon. If I supply text for the SQLUpdate property will that stop the default text being executed? If that's the case, suppose I set SQLUpdate->Text = 'select 1'. Would that mean no updates were ever sent to the server for that Query (I know what I'm suggesting is pretty pointless, I'm just trying to determine the way it works)? If that's the case would the changes be seen in the Query on the client side?

Also, if I set the text for UpdateSQL does that not mean I have to supply SQL to update every field in the query as I don't know which ones will be edited?

If you use the SQLUpdate property then on updating a dataset only this SQL query will be used. In your example it means that only the 'select 1' query will be executed.

If you use SQLUpdate, then you should create a query for all fields that may be updated. In this case if the specified field was not changed, an old value will be used.

OK Dimon. Thanks.

Dimon, I went to try this tonight but how do I represent the new values?

e.g. Suppose the query was

select A,B,C,D from Tbl; (Tbl has primary index on A)

and B was the only field ever edited in the query.

What do I put in place of X & Y in the following?

update Tbl set B=X where A=Y;

It's OK Dimon I seen this in the mydac help

Use the SQLUpdate property to specify a SQL statement that will be used when applying an update to a dataset. Statements can be parameterized queries. Names of the parameters should be the same as field names. The parameters prefixed with OLD_ allow to use current values of fields prior to the actual operation.
To create a SQLUpdate statement at design-time, use the query statement editor.
Example
UPDATE Orders
set
ShipName = :ShipName,
WHERE
OrderID = :Old_OrderID

It is good to see that this problem has been solved. If any other questions come up, please contact me.

I've only just realised how bad this feature is. I've got a table with 6 columns that make up the primary index and around 20 other columns most of which are type float. This means that the default code generated by MyDAC is unlikely to work as it attempts to find a record that matches the current one in every column (comparing floats isn't exactly precise).

In order for me to use the MyDAC components for updates I've got to set the UpdateSQL to

update Tbl set Fld1=:Fld1, ..... [repeat 20 times]
where Key1=:Old_Key1 [repeat 5 times]

and then every time I change a column name or add a column I've got to remember to go back and edit the UpdateSQL. It also means the server is being sent a request to update 20 fields when maybe only 1 has been edited.

How come the MyDAC code doesn't just make the WHERE statement consist only of primary index columns?

Is it safe for me to presume that, unless I've had a "Update failed. 0 records found" exception, my other MySQL components are managing to update their UpdateTable correctly?

At the very least would you not be better with a KeyFields property that the user can fill in. This could be used with the UpdateTable property so the TMyQuery component can generate more succinct and correct UpdateSQL.

hello,

If you don't specify your query text in the UpdateSQL property, the UPDATE query will be generated automatically only for the fields that have been changed and the WHERE condition will use only key fields, if you have to add fields to the WHERE condition, you can specify the required fields in the MyQuery.KeyFields property.

Forget it. I've just noticed that there is already a KeyFields property that is used for that precise purpose. My apologies.

hello,

If you have any other questions, feel free to contact us.

AlexP wrote:hello,

If you don't specify your query text in the UpdateSQL property, the UPDATE query will be generated automatically only for the fields that have been changed and the WHERE condition will use only key fields, if you have to add fields to the WHERE condition, you can specify the required fields in the MyQuery.KeyFields property.

Hi Alex, thanks for the reply but in my experience what you say isn't true. The code generated by TMyQuery (when you leave UpdateSQL blank) will only update those fields you have changed BUT from what I can see the WHERE condition includes every field in the UpdateTable unless (and I only discovered this today) you specify the key fields in the KeyFields property. That certainly seems to be true for multi-table queries at least unless this has been changed since 5.90. Otherwise there would've been little point in me starting the thread (3 years ago) in the first place.