Re: list rows with no recent updates
One option would be to add a column to the table with a last_updated timestamp. Everytime you update the row, update the last_updated field with the current timestamp. Therefore you could just query the timestamp column to get recently updated rows (or not so recently updated) as you please. -- Adam Alkins || http://www.rasadam.com On 14 June 2010 16:02, MadTh madan.feedb...@gmail.com wrote: Hi, I ran a update command on around 2700 rows inside a mysql database table which has around 3000 table rows to change the ( say) price of each item ( with unique ID. unique product code). like: mysql UPDATE tbl_xyz set listprice='9.45' where prod_id='3069' and prod_code='a0071'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 How can I list rows with no recent updates ( or the once where the above updates were not done) or say with no updates in last 2 hours? Thank you.
list rows with no recent updates
Hi, I ran a update command on around 2700 rows inside a mysql database table which has around 3000 table rows to change the ( say) price of each item ( with unique ID. unique product code). like: mysql UPDATE tbl_xyz set listprice='9.45' where prod_id='3069' and prod_code='a0071'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 How can I list rows with no recent updates ( or the once where the above updates were not done) or say with no updates in last 2 hours? Thank you.
RE: list rows with no recent updates
The only way I could think of is to have a column that's an auto updated timestamp and then just query using that time. `updated_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP So for your mass update, I'd SET @updated_time = NOW(); and then you could use that in your future query where @updated_time +/- some fuzzy amount of seconds. -Original Message- From: MadTh [mailto:madan.feedb...@gmail.com] Sent: Monday, June 14, 2010 2:02 PM To: mysql@lists.mysql.com Subject: list rows with no recent updates Hi, I ran a update command on around 2700 rows inside a mysql database table which has around 3000 table rows to change the ( say) price of each item ( with unique ID. unique product code). like: mysql UPDATE tbl_xyz set listprice='9.45' where prod_id='3069' and prod_code='a0071'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 How can I list rows with no recent updates ( or the once where the above updates were not done) or say with no updates in last 2 hours? Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: list rows with no recent updates
Do you have a timestamp field on this table? There's no way of seeing when a row was last updated unless you have a timestamp field that automatically updates for any change (that's *any* change - not necessarily the ones you want to keep track of) or creating your own and updating them either on the update statement itself or in a trigger. You can pretty much tell when the last time an entire table was updated by the date on the MYD or ibd file. I'm assuming you don't want to constantly parse the binlog or general log. On Mon, Jun 14, 2010 at 4:02 PM, MadTh madan.feedb...@gmail.com wrote: Hi, I ran a update command on around 2700 rows inside a mysql database table which has around 3000 table rows to change the ( say) price of each item ( with unique ID. unique product code). like: mysql UPDATE tbl_xyz set listprice='9.45' where prod_id='3069' and prod_code='a0071'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 How can I list rows with no recent updates ( or the once where the above updates were not done) or say with no updates in last 2 hours? Thank you. -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: list rows with no recent updates
Hi, Thank you all for your prompt response. Unfortunately timestamp file isn;t there, so I will find some other way to do it. Seems timestamp is a valuable field ( unless you want to save resource on generating timestamps on a very busy table). Thanks
RE: list rows with no recent updates
Easy enough to rectify http://dev.mysql.com/doc/refman/5.0/en/alter-table.html ALTER TABLE `tbl_xyz` ADD COLUMN `updated_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `prod_id`; Personally I put a 'created_on' and an 'updated_on' column for mostly every table I create. 3000 rows is nothing. A mere blink of an eye to mySQL. The database I'm using is almost a Billion (yes, with a B, and I mean a good ol' USA 10^9 Billion, not that goofy long scale 10^12 Billion*) rows and 90GB. So don't worry about it. Plus it's stored internally as an integer (timestamp) *http://en.wikipedia.org/wiki/Long_and_short_scales -Original Message- From: MadTh [mailto:madan.feedb...@gmail.com] Sent: Monday, June 14, 2010 2:23 PM To: mysql@lists.mysql.com Subject: Re: list rows with no recent updates Hi, Thank you all for your prompt response. Unfortunately timestamp file isn;t there, so I will find some other way to do it. Seems timestamp is a valuable field ( unless you want to save resource on generating timestamps on a very busy table). Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
