Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email email@example.com
From: Baron Schwartz (baronxaprb.com)
Date: Wed Jul 11 2007 - 13:49:57 CDT
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.
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql