It's so obvious it must be ingenious! I think that is what I shall do.
Mike Hillyer
www.vbmysql.com
-----Original Message-----
From: Jeremy Zawodny [mailto:Jeremy@stripped]
Sent: Tuesday, May 06, 2003 12:07 AM
To: Mike Hillyer
Cc: mysql@stripped
Subject: Re: Comparing two snapshots of a table to produce a change
report
On Mon, May 05, 2003 at 03:37:59PM -0600, Mike Hillyer wrote:
> Hi Everyone;
>
> I am trying to work on a solution for an application I am working on.
>
> Our app tracks data for customers who are sent a new data file every
> month that is generated from a query outputted to a CSV file.
Okay.
> I need to implement a system whereby the file can be accompanied by
> a report of changes to the file over the course of the month. We
> were thinking that each time an export is done, the data table can
> be copied to another table by creating a table using a SELECT SQL
> statement, that way we would have historical snapshots of the table
> (space is not an issue).
Or you could diff the two month's CSV files? It's not MySQL-specific
but should work.
> My question is: can anyone think of a query to get the difference
> between a snapshot and the current table? (Return all rows where any
> field is has changed)?
Sure. Several ways. One of the talks at the MySQL Conference focused
on this very problem (almost). When the notes go on-line it'll be
helpful.
How about this.
Put a timestamp column on the table. Use that to find all the rows
that have changed since last month. Then compare those rows to last
month's to figure out *what* changed in each row.
Jeremy
--
Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
<Jeremy@stripped> | http://jeremy.zawodny.com/
MySQL 4.0.8: up 91 days, processed 2,901,997,129 queries (366/sec. avg)
