At 9:35 AM +0200 4/2/09, Andreas Pardeike wrote:
>Hi,
>
>I have a table 'test'
>
>+---------+--------------+------+-----+-------------------+----------------+
>| Field | Type | Null | Key | Default | Extra |
>+---------+--------------+------+-----+-------------------+----------------+
>| id | int(11) | NO | PRI | NULL | auto_increment |
>| foo | varchar(255) | NO | | NULL
>| |
>| bar | varchar(255) | NO | | NULL
>| |
>| payload | longblob | YES | | NULL
>| |
>+---------+--------------+------+-----+-------------------+----------------+
>
>where 'payload' size usually is quite big!
>
>Now I want to manipulate a number of rows in a non-trivial way and at the
>same time set 'id' for those rows to a new number in the sequence. I.e.
>
>UPDATE test SET id = NEXT_INSERT_ID(), foo = some_function(foo) WHERE bar ...
>
>My motivation is that this table has several consumers in other applications
>that keep track of the biggest 'id' they have seen between polls and thus
>can keep track of new and changed rows.
>
>Right now, I solve this problem by copying the rows to a temporary table,
>then delete them and insert them from the temporary table but this moves my
>huge payload around which I really want to avoid.
>
>How can I solve this in a better way?
>
>/Andreas Pardeike
Add a column of type timestamp which, by default, will be updated
every time a record is inserted or updated. Then the other
applications can simply select records with timestamp >
last_poll_time.
steve
--
+--------------- my people are the people of the dessert, ---------------+
| Steve Edberg http://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center sbedberg@stripped |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+---------------- said t e lawrence, picking up his fork ----------------+
