CODEWITHSUNDEEP
mysql
yudun1989
yudun1989

Reputation: 996

mysql set one item id to the auto_increment max id

for example,there are several items in a table

 id |name|info
 1  |Jim |Male
 2  |Rob |Male

the id of the table is auto_increment, and now i want to update Jim's row to the bottom of Rob ,and get the newest id, aka 

 id |name|info
 2  |Rob |Male
 3  |Jim |Male

and get the id of (3), what's the sql expression?

Upvotes: 0

Views: 1095

Answers (2)

Edgar
Edgar

Reputation: 168

Since you can't select the max(id) in a the same update query, you have to define the max(id) as a variable. The query you need :

SET @max = (SELECT max(`id`) FROM `table` FOR UPDATE);
UPDATE `table` SET `id` = @max+1 WHERE `name` = "Jim";

EDIT : You are not supposed to update an id since it's an unique identifier. If you want to use sorting methods, you should add an integer column "position".

Upvotes: 4

echo_Me
echo_Me

Reputation: 37233

there is no update for autoincrement column you could delete Jims row and then insert it again and it will be id = 3

 DELETE FROM `table` where id = 1

then

 INSERT INTO `table` (name , info) VALUES ('jim' , 'male')

Upvotes: 1

Related Questions