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 sbedb...@ucdavis.edu |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+---------------- said t e lawrence, picking up his fork ----------------+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
