CODEWITHSUNDEEP
sql-serversql-update
Arklur
Arklur

Reputation: 183

How to UPDATE 1 row with multiple rows?

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:

enter image description here

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:

enter image description here

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.

Upvotes: 2

Views: 3095

Answers (3)

PSK
PSK

Reputation: 17953

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'

Upvotes: 3

EzLo
EzLo

Reputation: 14209

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'

Upvotes: 0

Marc Guillot
Marc Guillot

Reputation: 6465

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

Upvotes: 2

Related Questions