How do I compare two datasets to see if one of them changed? 1

[wmeurer]

I have two tables, TableA and TableB.

TableB holds a copy of some records in TableA. TableB is read only.

TableA is live--rows are added, deleted, and updated regularly.

I need to write a script to see if rows from TableA which correspond to rows that exist in TableB (by corresponding ID field) have been modified, and if they have, I need to copy the new version of the row from TableA to TableB.

My first guess was to do some sort of LEFT JOIN WHERE NULLs existed after comparing each column for exact match. Ugly.

My second guess was to do:

INSERT INTO TableB ([cols])
SELECT * FROM TableA WHERE NOT EXISTS (SELECT * FROM TableB WHERE col1=TableA.col1, col2=TableA.col2, etc.).

These feel like really dirty solutions.

What should I do? Thanks a bunch.

 

[Kozusnik]

Why are you keeping two tables with the same info?

Anyway, how about this?

truncate tableB;
insert into tableB (select * from tableA);

Mark

 

[wmeurer]

TableB is essentially an archive table (it exists in a different DB on the same server).

Since TableA allows DELETEs, if I truncate TableB and then just copy from TableA, I'll lose records from TableB.

The rule is, keep in TableB the latest version of every row that has existed in TableA.

 

[Kozusnik]

Gotcha.

I hope there aren't a lot of fields. I think you'll have to do something like this...

update tableA,tableB
set
tableB.field1=tableA.field1,
tableB.field2=tableA.field2, (etc...)
WHERE tableA.key=tableB.key

I gotta run, but I'll try to refine it later.

Mark