Following the two answers I received (none of which was complete so I wrote my own), what I eventually did is as follows: UPDATE Table AS target INNER JOIN ( select category, appearances_sum from Table T inner join ( select category as cat, sum(appearances) as appearances_sum from Table group by cat ) as agg where … Read more
Above example is not Co-related Sub-Query. It is Derived Table / Inline-View since i.e, a Sub-query within FROM Clause. A Corelated Sub-query should refer its parent(main Query) Table in it. For example See find the Nth max salary by Co-related Sub-query: SELECT Salary FROM Employee E1 WHERE N-1 = (SELECT COUNT(*) FROM Employee E2 WHERE … Read more
If you need this often, and/or the count is an integral part of your Tab1 model, you should use a hybrid property such as described in the other answer. If on the other hand you need this just for a single query, then you could just create the scalar subquery using Query.label(), or Query.as_scalar(): count_stmt … Read more
DB2 does indeed support joins in an UPDATE statement, only not the way you think — DB2 follows the SQL ANSI standard: UPDATE Sales_Import SI SET Sales_Import.AccountNumber = ( SELECT RAN.AccountNumber FROM RetrieveAccountNumber RAN WHERE SI.LeadID = RAN.LeadID ) The above assumes that LeadID uniquely identifies records in RetrieveAccountNumber, otherwise you will get an error … Read more