Sql Update Row With Unique id-CodePudding

hello i would like to update a row in my database but want this row to get a new unique id when its updated

example

sql = "UPDATE users SET username = 'timi' "

i would like timi to have a new unique id which mean if last id in the table is 5 i would want it to get a new unique id of 6 i dont want to write new sql to get the last id then increment and use. i want to use best practice thanks

CodePudding user response：

Join with a subquery to get the maximum ID from the table.

UPDATE users AS u
JOIN (
    SELECT MAX(id) AS maxid
    FROM users
) m
SET u.username = 'timi',
    u.id = m.maxid   1
WHERE <condition to select the row to update>

CodePudding user response：

// i also discovered more simple to go around it wihtout using join or complex structures


sql = "UPDATE users SET username = 'timi' , id = (SELECT MAX(id)   1 FROM user) WHERE id = 5";