Update Value in Datatable from Another Datatable

Efficient way for updating data in a data table from another data table

You can try it this way

dtOriginal.Merge(dtNew);

Update Datatable with another

As far as I can tell from your schema above you just need to write and execute this SQL.

Table1 is you table with dates to update, Table2 is your second table with dates

update t1 set t1.ECAD  = [t2].[Date] from Table1 t1 
                         inner join Table2 t2  ON t2.Ser = t1.Ser

But if you want to work with two DataTables already in memory then you could use

// Loop over the table with the unique Ser value and with the date to transfer
foreach (DataRow r in dtARIAA.Rows)
{
      // Get the rows in the destination table with the same "Ser" value
      DataRow[] destRows = dtReport.Select("Ser = " + r["Ser"]);

      // Now update the destination table rows with the Date value
      foreach (DataRow row1 in destRows)
          row1["ECAD"] = r["Date"];
}
dtReport.AcceptChanges();

How to update a datatable with another datatable?

You should not try to modify something from within LINQ queries. It's bad practise if LINQ queries cause side-effects. But you can collect the informations you need to update:

var updateQuery = from r1 in datatable1.AsEnumerable()
                  join r2 in datatable2.AsEnumerable()
                  on r1.Field<string>("header") equals r2 .Field<string>("header")
                  select new { r1, r2 };
foreach(var x in updateQuery)
{
    x.r1.SetField("state", x.r2.Field<string>("state"));
}

The query is really just a query, it is only executed once at the foreach, but also everytime you will access the updateQuery-variable due to LINQ's deferred execution. If you don't want this you have to create a collection, f.e. with ToList or ToArray.

Updating DataTable column from another DataTable

You can of course just iterate all records yourself and match them up. But you can also set up a relationship between the DataTables in the dataset, then create a computed column in the records datatable that pulls a parent column's value. See this example: http://windowsdevcenter.com/dotnet/2003/05/26/datacolumn_expressions.html

See this article on how to actually add relationships: http://msdn.microsoft.com/en-us/library/ay82azad%28v=vs.71%29.aspx

Toward the bottom:

Parent.ColumnName Column in a parent table

So after you create the relationship, you'd do something like: ds["Table2"].Columns.Add("Timestamp", typeof(DateTime), "Parent.Timestamp")

Update a data.table based on another data table

Here's how I would approach this. First, I will expand DT_new according to the unique values combination of x columns of both tables using binary join

res <- setkey(DT_new, x)[unique(c(x, DT_old$x))]
res
#    x  y  v  z
# 1: b  9  2  9
# 2: c  6 NA  9
# 3: d 10 10  9
# 4: a NA NA NA

Then, I will updated the two columns in res by reference using another binary join

setkey(res, x)[DT_old, `:=`(y = i.y, v = i.v)]
res
#    x  y  v  z
# 1: a  1  1 NA
# 2: b  3  2  9
# 3: c  6  3  9
# 4: d 10 10  9

---

Following the comments section, it seems that you are trying to join each column by its own condition. There is no simple way of doing such thing in R or any language AFAIK. Thus, your own solution could be a good option by itself.

Though, here are some other alternatives, mainly taken from a similar question I myself asked not long ago

Using two ifelse statments

setkey(res, x)[DT_old, `:=`(y = ifelse(is.na(y), i.y, y), 
                            v = ifelse(is.na(v), i.v, v))]

Two separate conditional joins

setkey(res, x) ; setkey(DT_old, x) ## old data set needs to be keyed too now
res[is.na(y), y := DT_old[.SD, y]]
res[is.na(v), v := DT_old[.SD, v]]

Both will give you what you need.

---

P.S.

If you don't want warnings, you need to define the corresponding column classes correctly, e.g. v column in DT_new should be defined as v= c(2L, NA_integer_, 10L)

---

Related Topics