From: Baron Schwartz (baronxaprb.com)
Date: Wed Jul 11 2007 - 13:49:57 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]