List:General Discussion« Previous MessageNext Message »
From:Andreas Pardeike Date:March 31 2009 1:44pm
Subject:Setting auto increment value in an update statement
View as plain text  
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
Thread
Setting auto increment value in an update statementAndreas Pardeike31 Mar