Update datatable field based on the value exist in another datatable in Asp.net c#

Question

Hi I have two datatable

table

id  isfav
1   0
2   0
3   0
4   0
5   0

favtable

id 
2
3

So I want to Update the table1 field isFav to 1 if the ids exist in FavTable.

Can anybody help me on this

Answer 1

In SQL...

UPDATE table SET isfav = 1 WHERE EXISTS(SELECT * FROM favtable WHERE favtable.id = table.id)

In Linq to SQL...

//Create DataContext first

foreach (var rec in from a in dc.table
                    join b in dc.favtable on a.id equals b.id
                    select a)
{
     a.isfav = true;
}

dc.SubmitChanges();

...or (more efficient)...

dc.ExecuteCommand("UPDATE table SET isfav = 1 WHERE EXISTS(SELECT * FROM favtable WHERE favtable.id = table.id)");

Answer 2

Since in the comments it was mentioned it is DataTable, you could use Join between these tables as below and update the field.

table1.AsEnumerable()
      .Join( table2.AsEnumerable(),
            t1 => t1.Field<int>("id"),
            t2 => t2.Field<int>("id"),
            (t1, t2) => new { t1 })
      .ToList()
      .ForEach(o => o.t1["isfav"] = 1);

Check this working code