Updateing dataset

Question

Lets say I have 2 datasets: A and B, that have the same columns. I want to get the 'distance path' - minimal operations needed to be done, to change datasets A to B.

I can assume, that A and B contain only in few differences and I'm looking for something better, then:

remove all A's elements
insert all B's elements into A

How can this be archived?

Johan · Answer 1 · 2011-06-11T10:18:56.483

If you know how to use SQL:

SELECT a.* FROM a 
LEFT JOIN b on (a.field1 = b.field1 AND a.field2 = b.field2 AND ....)
WHERE b.field1 IS NULL

Will give you all fields in A that are not in B.

Now do

INSERT INTO b
  SELECT a.* FROM a 
  LEFT JOIN b on (a.field1 = b.field1 AND a.field2 = b.field2 AND ....)
  WHERE b.field1 IS NULL

And then do (or don't do depending on your needs).

DELETE b FROM b
LEFT JOIN a ON (a.field1 = b.field1 and a.field2 = b.field2 AND ...)
a.field1 IS NULL

Now table a and b will be the same.

Delphi code like this should do the trick, but the exact code depends on your database and the query components used.

procedure TForm1.equalize(A, B: TDataset);
var
  tablenameA: string;
  tablenameB: string;
  MyQuery: TQuery;
begin
  tablenameA:= IProviderSupport(A).PSGetTableName;
  tablenameB:= IProviderSupport(B).PSGetTableName;
  MyQuery:= TQuery.Create(self);
  MyQuery.Database:= .....
  MyQuery.SQL.Text:= ' INSERT INTO '+tablenameA+' .....
  MyQuery.ExecSQL;
end;

The SQL solution is IMHO the faster, because it will run on Server, so will be faster for big amount of data. But IMHO you didn't answer the two points: "remove all A's elements + insert all B's elements into A". A is still there. Please add a `DELETE * FROM B` statement into your answer. ;) — Arnaud Bouchez, Jun 11 '11 at 08:39

score 2 · Accepted Answer · answered Jun 10 '11 at 23:58

If you want to find out how to transform A into B, you need to know what the difference between A and B is. There's a very simple general algorithm for that, though it might be more complicated depending on how many fields are in your dataset and how many of them can be different from A to B. But here's the general idea:

Sort both datasets. Make sure to sort on the exact same criteria. (At this point, if they both contained the same set of data elements, the two lists of their contents would be identical if viewed side by side.)
Begin with the first item of both datasets. Compare the two items with a comparison that can return less than, equal or greater than.
- If A < B, note the difference and move to the next row of A.
- If A > B, note the difference and move to the next row of B.
- If A = B, advance both datasets to the next row.
Repeat the compare-and-advance step until you've reached EOF on both datasets.

Once you're done, you'll have a full list of differences between A and B, which will make the steps needed to transform A to B simple to calculate.

Updateing dataset

2 Answers2