Hello guys, I know this is a strange request; I need to run the following via update directly without fetching the entities first... believe me that given the constraints of my db design, version and indexes I must reproduce this exact scenario, I've watered down the real query and changed the table names for explanation purposes:

** by temporal tables I meant: derived tables ** the reason for the double derived table is for optimization of the innermost derived table via materialization ** I'm using 5.6.2 and .net core 3.0

UPDATE table1 t1
JOIN
  (SELECT tmp.id
   FROM
     (SELECT t.id
      FROM table1 t inner join table 2 tt on (t.id=t2.id) 
      WHERE t.client_id = 1776 and tt.id_product = 22
       LIMIT 11) tmp
  ) tmp2 ON t1.id = tmp2.id
SET t1.order_id = 7857183;

Please advice

I was able to solve it using the technique described in the documentation, with a few tricks here and there using the low level API and adapter's UpdateEntitiesDirectly method, the hardest part was finding the correct way to use the aliases.

https://www.llblgen.com/documentation/5.1/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/gencode_derivedtabledynamicrelation.htm

Good you figured it out. As you said, using DerivedTable two times and DynamicRelation should result in something like your scenario. Example:

Given this sql

UPDATE Orders
SET Freight = 5
FROM
    (SELECT tmp.CustomerId FROM
            (SELECT CustomerId 
            FROM Customers
            WHERE Country = 'USA') tmp
    ) tmp2
    INNER JOIN Orders o 
    ON tmp2.CustomerId = o.CustomerId

... this would be the code to reproduce it:

// tmp
var tmpFields = new ResultsetFields(1);
tmpFields.DefineField(CustomerFields.CustomerId, 0);
var tmpDefinition = new DerivedTableDefinition(
                tmpFields, "tmp", new PredicateExpression(CustomerFields.Country == "USA"));

// tmp2
var relation = new DynamicRelation(tmpDefinition, JoinHint.Inner,
                        EntityType.OrderEntity, "o",
                        (new EntityField2(CustomerFieldIndex.CustomerId.ToString(), "tmp2", typeof(string))
                            .Equal(OrderFields.CustomerId.SetObjectAlias("o"))));

// tmp2
var tmp2Fields = new ResultsetFields(1);
tmp2Fields.DefineField(CustomerFields.CustomerId, 0, "CustomerId", "tmp");

var tmp2Definition = new DerivedTableDefinition(
                tmp2Fields, "tmp2", null, new RelationCollection(relation));

// root relation
var rootRelation = new DynamicRelation(tmp2Definition);
var bucket = new RelationPredicateBucket();
bucket.Relations.Add(rootRelation);

// set
var newValues = new OrderEntity { Freight = 5m };

// update
using (var adapter = new DataAccessAdapter())
{
    adapter.UpdateEntitiesDirectly(newValues, bucket);
}