如何用多行更新 1 行? [英] How to UPDATE 1 row with multiple rows?

问题描述

这只是一个简化的例子，我知道这似乎是错误的，但我认为这是问我问题的最简单的方式.

This is just an simplified example, I know it seems really wrong, but thought this is the simpliest way to ask my question.

假设我有一个 Person 和一个电子邮件"表.每个人可以有 2 个电子邮件.

Let's say I have a Person and an 'Email' table. Each person can have 2 e-mails.

这是一个小例子:

出于某种原因，我们决定将 Person 表扩展为 2 列:FirstEmail 和 SecondEmail，因此在更新后，结果看起来像这样:

For some reason, we decide to extend our Person table with 2 columns: FirstEmail, and SecondEmail, so after an update, the results looks like something like this:

我可以用一条语句UPDATE 表格吗?例如，我试过这个，但它不起作用(不给出错误，只有 FirstEmail 被填充，SecondEmail 是 NULL>).

Can I UPDATE the table with a single statement? For example, I tried this, but it doesn't work (doesn't give error, only the FirstEmail is filled, the SecondEmail is NULL).

UPDATE Person
SET FirstEmail = CASE WHEN e.Type = 'FIRST' THEN e.Value ELSE FirstMail END,
    SecondMail = CASE WHEN e.Type = 'SECOND' THEN e.Value ELSE SecondMail END
FROM Person p
INNER JOIN Email e ON (p.Id = e.PersonId);

正如我所见，MERGE 不能处理多行，因为我用类似的方法得到了这个错误:

And as I see MERGE can't work with multiple rows, because I get this error for a similar approach:

MERGE 语句多次尝试更新或删除同一行.当目标行匹配多个源行时会发生这种情况.MERGE 语句不能多次更新/删除目标表的同一行.细化 ON 子句以确保目标行最多匹配一个源行，或者使用 GROUP BY 子句对源行进行分组.

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

推荐答案

以下查询应该适合您.

UPDATE P
SET P.FirstEmail = F.Value, 
    P.SecondEmail= S.Value
FROM Person P
INNER JOIN EMAIL F ON F.PersonId=P.ID AND F.TYPE='FIRST'
INNER JOIN EMAIL S ON S.PersonId=P.ID AND S.TYPE='SECOND'

如果您没有两个 Person 的电子邮件，在这种情况下，您可以将 INNER JOIN 更改为 LEFT JOIN

If you don't have both the emails for Person, in that case you can change the INNER JOIN to LEFT JOIN

UPDATE P
SET P.FirstEmail = F.Value, 
    P.SecondEmail= S.Value
FROM Person P
LEFT JOIN EMAIL F ON F.PersonId=P.ID AND F.TYPE='FIRST'
LEFT JOIN EMAIL S ON S.PersonId=P.ID AND S.TYPE='SECOND'

这篇关于如何用多行更新 1 行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！