How can I make this complex update query work?

Question

SELECT
    (SELECT 
     IIF(IsNull(sum(b.AmountCharged) - sum(b.AmountPaid)),
                a.Balance, 
                (sum(b.AmountCharged) - sum(b.AmountPaid)))
     FROM tblCurrentTransaction AS b
     WHERE b.TenantTransactionID <= a.TenantTransactionID 
                AND b.Tenant = a.Tenant
     GROUP BY b.Tenant
    ) AS TrueBalance, a.TenantTransactionID
    FROM tblCurrentTransaction AS a
    ORDER BY a.Tenant, a.TenantTransactionID;
UNION
UPDATE tblCurrentTransaction SET tblCurrentTransaction.Balance = TrueBalance
WHERE tblCurrentTransaction.TenantTransactionID = a.TenantTransactionID;

Basically what happens is I get a result set from the first query, then I match it's TenantTransactionID with the update query. However Access complains: "An action query cannot be used as a row source"

How can I fix this?

This is the query without the UNION

UPDATE tblCurrentTransaction SET tblCurrentTransaction.Balance = (SELECT
    (SELECT 
     IIF(IsNull(sum(b.AmountCharged) - sum(b.AmountPaid)),
                a.Balance, 
                (sum(b.AmountCharged) - sum(b.AmountPaid)))
     FROM tblCurrentTransaction AS b
     WHERE b.TenantTransactionID <= a.TenantTransactionID 
                AND b.Tenant = a.Tenant
     GROUP BY b.Tenant
    ) AS TrueBalance
    FROM tblCurrentTransaction AS a
    WHERE a.TenantTransactionID = tblCurrentTransaction.TenantTransactionID
    ORDER BY a.Tenant, a.TenantTransactionID;
);

But it doesn't do anything, and Access complains "Operation must use an updateable query"

This is the query that gathers the data This query returns the true balance, and the transaction ID it belongs to. This is what I need to insert into the table.

SELECT (SELECT IIF(IsNull(sum(b.AmountCharged) - sum(b.AmountPaid)),a.Balance, (sum(b.AmountCharged) - sum(b.AmountPaid)))
          FROM tblCurrentTransaction AS b
          WHERE b.TenantTransactionID <= a.TenantTransactionID AND b.Tenant = a.Tenant
          GROUP BY b.Tenant
        ) AS TrueBalance, a.TenantTransactionID
FROM tblCurrentTransaction AS a
ORDER BY a.Tenant, a.TenantTransactionID;

Answer 1

You need to reorder your query, so you're performing an UPDATE ... WHERE (criteria) IN (SELECT ...). Basically, use your SELECT as a subselection to provide a resultset for the IN.

Answer 2

SELECT UNION UPDATE. Wow. Never seen anybody try that one before.

If this really has to be done in SQL, I'd say rewrite the whole thing as an UPDATE with subqueries.

If it doesn't really have to be done in SQL, I'd say rewrite the whole process so the logic is in your application language and the SQL queries are simple UPDATEs.

Edit: OP and I went through quite some adventures, as you can see in the comments. The final resolution was to use a scratch table to do the calculations in and push the results from there back into tblCurrentTransaction. I blame Access.

Answer 3

Try this:

UPDATE tblCurrentTransaction SET tblCurrentTransaction.Balance = i.TrueBalance
FROM ((SELECT 
    IIF(IsNull(sum(b.AmountCharged) - sum(b.AmountPaid)), a.Balance, (sum(b.AmountCharged) - sum(b.AmountPaid)))
    FROM tblCurrentTransaction AS b
    WHERE b.TenantTransactionID <= a.TenantTransactionID AND b.Tenant = a.Tenant
    GROUP BY b.Tenant
  ) AS TrueBalance, a.TenantTransactionID
  FROM tblCurrentTransaction AS a
 )AS i
WHERE tblCurrentTransaction.TenantTransactionID = i.TenantTransactionID;

Answer 4

I have a recent experience similar to this. I needed to update a join and discovered that Access won't handle complex update queries like that. I suspect that this situation falls into that bucket, but that's really hard to establish since the Access docs aren't that definitive on what it will and won't do. I ended up moving the work to SQL Server.

Bottom line, I agree with CHAOS that you should simplify the query by surrounding it with app logic.

Answer 5

See MSDN:

ACC: Update Query Based on Totals Query Fails

[Quote] When you run an update query, you receive the following error message:

Operation must use an updatable query.

This error message occurs when the values in the query's Update To row are fields in ... a select query or subquery that contains aggregate (totals) functions...

This behavior is a design limitation. [Unquote]

[I hear someone here is down-voting people for dissing the Access database engine. Oh well, I guess I'll just have to put my reputation on the line...]

The Access database engine has many "design limitations" but for me its non-Standard (ISO/ANSI SQL Standard, that is), ambiguous and severely limited UPDATE syntax is its very worst feature. None of the workarounds are acceptable to me and is one of the genuine reasons why I moved all my serious projects to SQL Server years ago.