SQL Server - JOIN in UPDATE statement

Question

I have a temp table with a number of non distinct customer records, and I want to update the same column in all rows for the same email address, if they have an existing value:

CustomerID | Email | Pref1 | Pref2 | Pref3
-----------------------------------------------------
1234    email1@domain.com    1    0    0
1235    email1@domain.com    1    1    0
1236    email1@domain.com    0    0    1
1237    email2@domain.com    0    0    0
1238    email2@domain.com    1    0    0

Should become:

CustomerID | Email | Pref1 | Pref2 | Pref3
-----------------------------------------------------
1234    email1@domain.com    1    1    1
1235    email1@domain.com    1    1    1
1236    email1@domain.com    1    1    1
1237    email2@domain.com    1    0    0
1238    email2@domain.com    1    0    0

Currently I have a while loop (loop over the rows that have yet to be updated), that contains an inner cursor to loop over the columns I want to update (Pref1,Pref2,Pref3, etc). This works, but takes forever on a larger recordset.

How can I modify the below:

UPDATE #table
SET Pref1 = 
  (
  SELECT MAX(IsNull(Pref1,0)) 
  FROM #table 
  WHERE Email = 'email1@domain.com'
  ) 
WHERE Email = 'email1@domain.com'

so that instead of passing in every email address, somehow refer to the email address of the record it is updating?

UPDATE #table
 SET Pref1 = 
  (
  SELECT MAX(IsNull(Pref1,0)) 
  FROM #table 
  WHERE Email = #table.email
  )

(The above query doesn't work, it simply updates that entire column for all records to 1 if it exists in that column). Should I be using some sort of update join?

score 4 · Accepted Answer · answered Feb 28 '12 at 19:22

If you are using SQL Server 2005 or later:

WITH maxvalues AS (
  SELECT
    *,
    MaxPref1 = MAX(Pref1) OVER (PARTITION BY Email),
    MaxPref2 = MAX(Pref2) OVER (PARTITION BY Email),
    MaxPref3 = MAX(Pref3) OVER (PARTITION BY Email)
  FROM #table
)
UPDATE maxvalues
SET
  Pref1 = ISNULL(MaxPref1, 0),
  Pref2 = ISNULL(MaxPref2, 0),
  Pref3 = ISNULL(MaxPref3, 0)

score 0 · Answer 2 · answered Feb 28 '12 at 18:29

You can do this with a derived table to find the maximum value of each preference per email address:

update #table
set Pref1 = mPref1,
    Pref2 = mPref2,
    Pref3 = mPref3
from #table t
  inner join (select Email, max(Pref1) mPref1, max(Pref2) mPref2, max(Pref3) mPref3
              from #table
              group by Email) m on m.Email = t.Email

score 0 · Answer 3 · answered Feb 28 '12 at 18:32

How about something like this? Of course you will need to adapt it to your environment but should be a good template to work with.

UPDATE 
#table1 

SET 
#table1.Pref1 = #table2.YourIsNullThingy

FROM
#table1 INNER JOIN #table2
    ON #table1.Email= #table2.Email

WHERE
#table2.Email IN (value1,value2,...)

lauh · Answer 4 · 2012-02-28T19:08:12.137

0

Give this a try:

UPDATE t1
SET t1.Pref1 = ot2.newPref1 
, t1.Pref2 = ot2.newPref2
, t1.Pref3 = ot2.newPref3 
FROM testTable AS t1
OUTER APPLY
(SELECT MAX(IsNull(t2.Pref1, 0)) AS newPref1
, MAX(IsNull(t2.Pref2, 0)) AS newPref2
, MAX(IsNull(t2.Pref3, 0)) AS newPref3 
FROM testTable t2 
where t2.e = t1.e)
ot2

edited Feb 28 '12 at 19:08

answered Feb 28 '12 at 19:00

lauh

22
2

SQL Server - JOIN in UPDATE statement

4 Answers4