Merge Datatable

I have two datatables. I am trying to merge them on the Date column and update the rows where there is a match and insert the rows where there is no match (exactly how a merge works in SQL).

OldTable

Date Value
1/1/19 33
1/2/19 30

NewTable

Date Value
1/2/19 29

Expected Result

Date Value
1/1/19 33
1/2/19 29

However, if I use the merge activity, UIPath gives me the following:

IncorrectResult

Date Value
1/1/19 33
1/2/19 30
1/2/19 29

What do?

Hello.
From the sound of it, you don’t actually want to do a merge, but instead you want to update the datatable with the new value, and if the date doesn’t exist, then add the row.

I don’t think you can update your datatable using query methods (I could be wrong), but you can do this with a For each row. Additional option would be to query your datatable to only the row being updated to make it go faster.

My preference is usually to query so it keeps the workflow less cluttered. This would look like this:

For each rNew In newTable
    Assign activity: matchedRows = oldTable.AsEnumerable.Where(Function(rOld) If(IsDate(rOld) And IsDate(rNew), Convert.ToDate(rOld("Date")) = Convert.ToDate(rNew("Date")), False) ).ToArray
    If matchedRows.Count > 0 //check if a match was found
        Assign activity: matchedRows.First("Value") = rNew("Value").ToString.Trim
    Else
        Add Data Row activity: rNew into oldTable

I hope this answers your question. - matchedRows is an array of Datarows, fyi

EDIT: made a few corrections

Regards.