Update statement with calculated fields in SELECT

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
dam54
Posts: 10
Joined: Thu 23 Jun 2011 19:26

Update statement with calculated fields in SELECT

Post by dam54 » Wed 28 Mar 2012 15:44

Hello - whenever we try to update a query that has a SELECT statement similar to the following:

SELECT
ISNULL(tbl1.fieldname, tbl2.fieldname) as fieldname
FROM table2 tbl2 left outer join table1 tbl1
on tbl2.id = tbl1.id

The upate statement generated drops the fieldname field completely and the value is never set.

The same thing happens with CASE statements in the SELECT.

Any ideas what is going on or how we can work around this?

We are using SQL Server Data Access Components for SQL Server
Version 6.1.4 for Delphi 6

Thanks in advance for you help!

AndreyZ

Post by AndreyZ » Thu 29 Mar 2012 14:34

Hello,

For such queries, you should write update SQL statements yourself and set them to the TMSQuery.SQLUpdate property.

Post Reply