sql | find updated rows

Create a helper table, containing rows md5 hashes:

CREATE TABLE IF NOT EXISTS T_ROWMD5 (num_id INT PRIMARY KEY, md5 VARCHAR(32), tcreate timestamp default now(), tmodified timestamp default now() on update now());

Get a rows hash from your main table you want to watch rows:

select md5(concat(coalesce(title,'empty'),coalesce(author,'empty'))) as hash from T_MAIN where num_id=MY_NUM_ID;

Write it into your helper table:

insert into T_ROWMD5 (num_id,md5) values (MY_NUM_ID,\"MYMD5FROMTHEPREVIOUSLINE\") on duplicate key update md5=\"MYMD5FROMTHEPREVIOUSLINE\";

Now check your main table rows for changed md5’s:

select num_id from T_MAIN where SOMEFIELDNAME=\"SOMEFIELDVALUE\" ) and md5(concat(coalesce(title,'empty'),coalesce(author,'empty'))) not in (select md5 from T_ROWMD5 where num_id=T_MAIN.num_id) limit 1;

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.