SEO

#1 (**permalink**) 02-28-2008, 06:48 AM

using one single sql statement, how do i update the auto_increment id
column of a row to have the new highest id in the table?

in other words: how do i make a row seem like it was just inserted?

i know how to do it with two statements, but i want to do it with one
to ensure nothing goes wrong:

$newid = get_value("select max(id) from user") + 1;
run_query("update table mytable set id = '$newid' where id = '$oldid' ");

hope someone can help. thanks!

#2 (**permalink**) 02-28-2008, 06:48 AM

update table mytable set id =last_insert_id() + 1 where id = $oldid

2007/7/11, Olav MÃ¸rkrid <olav.morkrid@gmail.com>:
>
> using one single sql statement, how do i update the auto_increment id
> column of a row to have the new highest id in the table?
>
> in other words: how do i make a row seem like it was just inserted?
>
> i know how to do it with two statements, but i want to do it with one
> to ensure nothing goes wrong:
>
> $newid = get_value("select max(id) from user") + 1;
> run_query("update table mytable set id = '$newid' where id = '$oldid' ");
>
> hope someone can help. thanks!
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=omelnyk@gmail.com
>
>

--
Sincerely yours,
Olexandr Melnyk
http://omelnyk.net/

#3 (**permalink**) 02-28-2008, 06:48 AM

thanks olexandr

my posting had a misprint. the select should be on "mytable" not
"user", so when i use your suggestion, i get an error:

mysql> update test set id = (select max(id) + 1 from test) where id = '$myid';
ERROR 1093 (HY000): You can't specify target table 'test' for update
in FROM clause

so how does one make a row id to appear as a newly inserted row,
without doing multiple queries?

#4 (**permalink**) 02-28-2008, 06:48 AM

what i want to do is to take an old row from maybe three weeks ago,
and make its id appear as if it was the newest inserted row in the
table. therefore last_insert_id() cannot be used.

i could introduce a timestamp column to achieve my goals, but for
certain reasons i would like to update the id if possible by
acceptable means.

#5 (**permalink**) 02-28-2008, 06:48 AM

2007/7/11, Olav MÃ¸rkrid <olav.morkrid@gmail.com>:
>
> thanks olexandr
>
> my posting had a misprint. the select should be on "mytable" not
> "user", so when i use your suggestion, i get an error:
>
> mysql> update test set id = (select max(id) + 1 from test) where id =
> '$myid';
> ERROR 1093 (HY000): You can't specify target table 'test' for update
> in FROM clause
>
> so how does one make a row id to appear as a newly inserted row,
> without doing multiple queries?

why do you need this?

you can do insert and use last_ionsert_id() within a transaction (in case
storage engine you're using supports it)

--
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=omelnyk@gmail.com
>
>

--
Sincerely yours,
Olexandr Melnyk
http://omelnyk.net/

#6 (**permalink**) 02-28-2008, 06:48 AM

Hi,

Olav Mørkrid wrote:
> mysql> update test set id = (select max(id) + 1 from test) where id =
> '$myid';
> ERROR 1093 (HY000): You can't specify target table 'test' for update
> in FROM clause

You will need to place the subquery in another subquery in the FROM clause so it is
materialized to a temorary table:

update test set id = (
select id + 1 from ( select max(id) as id from test ) as x
)
where id = $myid;

If you need to do this kind of query on insert, there are other things to think about
too. See http://www.xaprb.com/blog/2006/04/20...n-generic-sql/

Baron

#7 (**permalink**) 02-28-2008, 06:48 AM

baron

your suggestion does the trick indeed. i take a deep bow!

thanks also for mentioning the related issues.

#8 (**permalink**) 02-28-2008, 06:48 AM

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?

#9 (**permalink**) 02-28-2008, 06:48 AM

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

#10 (**permalink**) 02-28-2008, 06:48 AM

i agree with the logic that mysql treats things as sets. my problem
can easily be solved by treating one row at a time. thanks again!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

giving a row the new highest id