How to UPDATE 1 row with multiple rows?

Question

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

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

Here is a small example:

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:

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);

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

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.

Whey would you denormalize your data here? Are you getting rid of the email table all together? — S3S, Apr 11 '18 at 13:41
Possible duplicate of [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Tab Alleman, Apr 11 '18 at 13:42
`"it doesn't work (doesn't give error...)"` [Explanation](https://stackoverflow.com/a/32386372/5070879) — Lukasz Szozda, Apr 11 '18 at 13:46
@scsimon of course it's just a simplified example to make my problem very easy to understand, what I'm actually need to do is more complex. So don't worry, I don't plan to do stupid thing like this :). — Arklur, Apr 11 '18 at 17:37

PSK · Accepted Answer · 2018-04-11T13:50:27.683

Following query should work for you.

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'

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'

Marc Guillot · Answer 2 · 2018-04-11T13:44:08.227

2

Using two subqueries the solution is trivial :

update Person set
       FirstEMail = (select top 1 Value from EMail where PersonId = Person.Id and Type = 'FIRST'),
       SecondEMail = (select top 1 Value from EMail where PersonId = Person.Id and Type = 'SECOND')

edited Apr 11 '18 at 13:44

answered Apr 11 '18 at 13:42

Marc Guillot

6,090
1
15
42

2

i would say add select top 1 with the subqueries for foolprrofness – RAHUL S R Apr 11 '18 at 13:43
@RAHULSR For foolproofness there should be explicit TOP + ORDER BY – Lukasz Szozda Apr 11 '18 at 13:50
@lad2025 you thnk thats necessory? since the where conditon is checking the exact match?? – RAHUL S R Apr 11 '18 at 16:11
@RAHULSR With exact match there is no need for TOP 1 – Lukasz Szozda Apr 11 '18 at 16:22
@lad2025 what if the same id has two FIRST Type val – RAHUL S R Apr 11 '18 at 16:23
id ->1 PersonID ->1 TYPE->FIRST,id->2 PersonID->2 Type -> FIRST – RAHUL S R Apr 11 '18 at 16:24
@RAHULSR Refering to your prev comment `since the where conditon is checking the exact match?? ` – Lukasz Szozda Apr 11 '18 at 16:25

score 0 · Answer 3 · answered Apr 11 '18 at 13:45

The problem with your update attempt is that a row with a particular email is never "First" and "Second" at the same time (so either FirstEmail is updated or SecondEmail is, but never both).

A solution is using 2 joins, one for each email:

UPDATE P SET
    FirstEmail = E1.Value,
    SecondEmail = E2.Value
FROM
    Person AS P
    LEFT JOIN Email AS E1 ON
        E1.PersonID = P.ID AND
        E1.Type = 'First'
    LEFT JOIN Email AS E2 ON
        E2.PersonID = P.ID AND
        E2.Type = 'Second'

How to UPDATE 1 row with multiple rows?

3 Answers3

Linked