mysql编号行比选择并更新行中的信息

Question

这有效:

set @type = ' ';  set@num = 1; select * from (select *, @num:= if(@type = firstname, @num + 1, 1) as num, @type := firstname  as dummy from table order by id desc) as table  where table.firstname = 'Mike' and table.num = '2'

这不是

set @type = ' ';  set@num = 1; Update table set table.firstname = 'me' where (select table.firstname from (select *, @num:= if(@type = firstname, @num + 1, 1) as num, @type := firstname as dummy from personal) as table where table.firstname ='Mike' and table.num = '2');

这会更新所有行

set @type = '';  set@num = 1; Update personal set personal.firstname = 'Mike' where personal.firstname in (select personal.firstname from (select firstname, lastname, password, id, @num:= if(@type = firstname, @num + 1, 1) as num, @type := firstname  as dummy from personal order by id desc) as t where t.firstname = 'mike' and num = '1');

关于更新单个选定行的任何想法

Answer 1

请尝试一下这个。如果您正在尝试更新最后创建的名称“Mike”的记录。

set @type = '';  set@num = 1; 
Update personal set personal.firstname = 'NewName' 
 where personal.id in 
 (select t.id from 
  (select firstname, lastname, password, id, 
         @num:= if(@type = firstname, @num + 1, 1)  as num, 
        @type := firstname  as dummy 
      from personal order by firstname,id desc) 
  as t where t.firstname = 'Mike' and num = '1');

或者，如果您想更新名称“Mike”的“上次创建”记录，则可以只运行更新而不使用任何变量。

Update personal set personal.firstname = 'NewName' 
 where personal.id in 
     (select t.id from 
        (select id
         from personal 
         where firstname = 'Mike'
         order by id desc LIMIT 1
        )as t 
      );

或者更好的是，您只需运行此更新即可

Update personal set personal.firstname = 'NewName' 
       where firstname = 'Mike'
Order by id DESC
Limit 1;