SQL 更新具有不同值的多行，其中它们与列表中的值匹配

Question

所以标题可能有点令人困惑。如果您可以建议更好的措辞，请告诉我，我会更新。

问题来了。我有一个包含数千行的表，我需要更新其中的几千行以存储最新的电子邮件数据。

例如:

[email protected] =>[email protected]

[email protected] =>[email protected]

我有一个旧电子邮件列表(“[email protected]”、“[email protected]”)和一个新电子邮件列表(“[email protected]”、“[email protected]”)。希望是简单地用类似的东西来做到这一点

UPDATE Table 
SET Email = ('<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="206e4557654d41494c60110e434f4d" rel="noreferrer noopener nofollow">[email protected]</a>','New<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="2b6e464a42476b1905484446" rel="noreferrer noopener nofollow">[email protected]</a>')
WHERE Email = ('<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="80cfece4c5ede1e9ecc0b1aee3efed" rel="noreferrer noopener nofollow">[email protected]</a>','<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="0847646c4d65696164483a266b6765" rel="noreferrer noopener nofollow">[email protected]</a>')

我希望这是有道理的。有任何问题就问吧。谢谢!

Answer 1

您可以使用 case 表达式:

update mytable
set email = case email
    when '<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="d897b4bc9db5b9b1b498e9f6bbb7b5" rel="noreferrer noopener nofollow">[email protected]</a>' then '<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="014f6476446c60686d41302f626e6c" rel="noreferrer noopener nofollow">[email protected]</a>'
    when '<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="eda28189a8808c8481addfc38e8280" rel="noreferrer noopener nofollow">[email protected]</a>' then 'NewEmai<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="acc0ec9e82cfc3c1" rel="noreferrer noopener nofollow">[email protected]</a>'
end
where email in ('<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="d897b4bc9db5b9b1b498e9f6bbb7b5" rel="noreferrer noopener nofollow">[email protected]</a>','<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="bff0d3dbfad2ded6d3ff8d91dcd0d2" rel="noreferrer noopener nofollow">[email protected]</a>')

或者更好的是，如果您有一个很大的值列表，您可以创建一个表来存储它们(例如 myref(old_email, new_email))并将其加入您的更新查询中，如下所示:

update t
set t.email = r.new_email
from mytable t
inner join myref r on r.old_email = t.email

更新/连接的实际语法因数据库而异 - 上面的 SQL Server 语法。