mysql - SQL:更新多行

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center。
                            
                        
                    
                
                                7年前关闭。
            
                    
我想用一个查询更新多行。我正在使用MySQL。

什么是正确的查询？

这是一个示例，其中我需要使用一个查询来更新多个姓氏。

开始：

name      last_name
______    ________
James     abcasd
Becky     hadsfd
Richard   adfdfadgg

完：

name      last_name
______    ________
James     Jamerson
Becky     Beckerdude
Richard   O'Nerdy

最佳答案

如果情况有限，可以使用Case Statement.

update yourTable set Last_Name = CASE When Name = 'James' then 'Jamerson'
                                 WHEN Name = 'Becky' then 'Beckerdude'..
                                 --then for each case
                                 END

编辑

如果在另一个表中有关系名称-NewLastName，则创建一个脚本：

select
'When Name = '''+CAST(Name as Varchar(50)+''' Then '''+Cast(LastName as Varchar(50)+'''
from yourTableWithRelation

这将生成所有When Name then LastName
然后将其添加到update

Update yourTable set Last_Name = CASE
--Paste here the generated
END

编辑2

update的另一种最佳方式，如果您在另一个表中获得了该关系：

Update T set T.Last_Name = T2.LastName from YourTableToUpdate T inner join TableWithNewLastName T2
on T1.Name = T2.Name