CODEWITHSUNDEEP
sqlsql-servert-sqljoinsql-update
David Whitten
David Whitten

Reputation: 199

SQL Update with join

Is there any other faster way to do an update besides with a join? Here's my query but it's a bit slow:

UPDATE @user_dupes
  SET ExternalEmail = ud2.Email
 FROM @user_dupes ud1
INNER JOIN(
SELECT Email, UserName 
  FROM @user_flat_emailtable_dupes
 WHERE EmailType = 2
   AND Email IS NOT NULL AND LEN(Email) > 0
) ud2
ON ud1.UserName = ud2.UserName

Thanks for any ideas

Upvotes: 2

Views: 1665

Answers (3)

Levin
Levin

Reputation: 2005

I believe this query will do the same thing (although you'd have to be sure to form @user_flat_emailtable_dupes with no duplicate usernames). I haven't checked to see if they have different execution plans. It looks like you're refining junky input, I mention this partly because I do a lot of that and find MERGE useful (all the more useful for me since I don't know how UPDATE FROM works). And partly because I hadn't ever used MERGE with variables. It appears to be the case that at least the target table must be aliased, or the parser decides @ud1 is a scalar variable and it breaks.

MERGE @user_dupes AS ud1
USING @user_flat_emailtable_dupes AS ud2
    ON emailType = 2 
   AND COALESCE(ud2.email, '') <> ''
   AND ud2.username = ud1.username
WHEN MATCHED THEN UPDATE
    SET externalEmail = ud2.email
    ;

Upvotes: 1

MatBailie
MatBailie

Reputation: 86716

A couple of changes, on top of what @Adrian said...

UPDATE
  ud1   -- @Adrian's change.  Update the instance that you have already aliased.
SET
  externalEmail = ud2.Email
FROM
  @user_dupes                   AS ud1
INNER JOIN
  @user_flat_emailtable_dupes   AS ud2
    ON ud1.UserName = ud2.UserName
WHERE
      ud2.EmailType = 2      -- Removed sub-query, for layout, doubt it will help performance
  AND ud2.Email IS NOT NULL
  AND ud2.Email <> ''        -- Removed the `LEN()` function

But possibly the most important past is to ensure you have indexes. The JOIN is necessary for this logic (or correlated sub-queries, etc), so you want the join to be performant.

An Index of (UserName) on @user_dupes, and an Index of (EmailType, Email, UserName) on @user_flat_emailtable_dupes. (This assumes ud2 is the smaller table, after the filtering)

With the indexes as specified, the change from LEN(Email) > 0 to Email <> '' may allow an index seek rather than scan. The larger your tables the more apparent this will be.

Upvotes: 4

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

If you are using SQL Server, you were almost there. It's just a little fix:

UPDATE ud1 --little fix here!
  SET ExternalEmail = ud2.Email
 FROM @user_dupes ud1
INNER JOIN
(
SELECT Email, UserName 
  FROM @user_flat_emailtable_dupes
 WHERE EmailType = 2
   AND Email IS NOT NULL AND LEN(Email) > 0
) ud2
ON ud1.UserName = ud2.UserName

Upvotes: 4

Related Questions