Olav Mørkrid wrote:
wait, let's make it even more interesting :)
what if you want to update more than one row, and each row should have
a successive new id. is that possible in one statement?
i tried just removing the where statement in barons suggestion, which
fails as i guess the select is computed only once prior to being used
in the update/set.
mysql> update test set id = (select id + 1 from ( select max(id) as id
from test ) as x);
ERROR 1062 (23000): Duplicate entry '424' for key 1
is it possible, or do i have to do the rows one by one?
I lack imagination right now, but I can't think of a scenario where this would work.
You are updating many rows with a single value (there is only one max(id) in the table,
after all). Remember SQL is supposed to treat things as sets, not work iteratively.
But you could write a stored procedure to iteratively do what you seek.
There are some other scenarios where I can imagine selecting a set of groupwise maximum
values, joining those to a set of current values, and updating the current values from
the groupwise max. But this is different: it matches a set of max-values to a set of rows.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
