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!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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:
update table mytable set id =last_insert_id() + 1 where id = $oldid
2007/7/11, Olav Mørkrid [EMAIL PROTECTED]:
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
Err..
you can do this:
update table mytable set id = (select max(id) + 1 from user) where id =
$oldid
but I would recommend to use a transaction
2007/7/11, Olexandr Melnyk [EMAIL PROTECTED]:
update table mytable set id =last_insert_id() + 1 where id = $oldid
2007/7/11, Olav Mørkrid [EMAIL
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
2007/7/11, Olav Mørkrid [EMAIL PROTECTED]:
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
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
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
baron
your suggestion does the trick indeed. i take a deep bow!
thanks also for mentioning the related issues.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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
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
11 matches
Mail list logo
