list rows with no recent updates

--001636c5a56e60b81f048903cdab
Content-Type: text/plain; charset=ISO-8859-1

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.

--001636c5a56e60b81f048903cdab--

Re: list rows with no recent updates

--0016e6d975bcedfb25048903f378
Content-Type: text/plain; charset=ISO-8859-1

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 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.
>

--0016e6d975bcedfb25048903f378--

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.feedback@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=gcdmg-mysql-2@m.gmane.org

Re: list rows with no recent updates

--0016e64c16a8277f87048903f942
Content-Type: text/plain; charset=ISO-8859-1

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 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

--0016e64c16a8277f87048903f942--

Re: list rows with no recent updates

--90e6ba180ed83ff08404890416f3
Content-Type: text/plain; charset=ISO-8859-1

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

--90e6ba180ed83ff08404890416f3--

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.feedback@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=gcdmg-mysql-2@m.gmane.org