Update records of one table with values of another table

Posts   
 
    
Posts: 8
Joined: 15-Mar-2007
# Posted on: 07-Apr-2007 17:01:27   

I want to update a field in a table and set the value of it in different rows with the values of a field in another table. I know that I should use "UpdateMulti" or "UpdateEntitiesDirectly" methods, but I don't know how to set the value of a field to a Select Clause.

For example :

Update T1 Set F1 = (Select F2 From T2 Where T1.PK=T2.PK)

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 09-Apr-2007 02:18:53   

You can achieve that using Relations and ExpressionToApply.

I think your sql can be rewritten as:

UPDATE T1 
SET F1 = F2 
FROM T2 
   INNER JOIN T2  ON T1.PK=T2.PK

Here is an Northwind example. The example updates Orders.ShipAddress with Customer.Address related.

// values to update
OrdersEntity newOrderValues = new OrdersEntity();
newOrderValues.Fields["ShipAddress"].ExpressionToApply = CustomersFields.Address + "...";

// set filter and relations to use
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(OrdersEntity.Relations.CustomersEntityUsingCustomerId);   

// update entities
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.UpdateEntitiesDirectly(newOrderValues, filter);
}

So, adapt the concept to your case and let us know if everything is ok wink

David Elizondo | LLBLGen Support Team
Posts: 8
Joined: 15-Mar-2007
# Posted on: 09-Apr-2007 11:02:07   

It is great. it is really my solution. thanks.