Update Query not updating all records in inner select

When I run the query below, it only updated 85 records, but the subquery produces around 250.  I'm not quite sure why only 85 accounts are updated wtih list.phonenumber

This updates only 85 records:

UPDATE Master
      SET master.homephone = list.phonenumber
FROM MASTER m
INNER JOIN
      (SELECT DISTINCT m.number, p.phonenumber FROM Master m (NOLOCK)
      INNER JOIN phones p ON p.number = m.number
      WHERE      p.phonetypeID = 1
                  AND (p.good <> NULL OR p.good <> 0)
                  AND LEN(p.phonenumber) = 10
      ) AS list ON m.Number = list.Number
WHERE m.homephone IS NULL OR m.homephone = ''


This returns 215 rows

SELECT DISTINCT m.number, p.phonenumber FROM Master m (NOLOCK)
      INNER JOIN phones p ON p.number = m.number
      WHERE      p.phonetypeID = 1
                  AND (p.good <> NULL OR p.good <> 0)
                  AND LEN(p.phonenumber) = 10
      
This returns 285,855 rrows

select m.number, m.homephone from master m WHERE m.homephone IS NULL OR m.homephone = ''
LVL 1
dba123Asked:
Who is Participating?
 
sudheeshthegreatConnect With a Mentor Commented:
distinct would apply on both the columns in the select list. so, here it would return a distinct set of m.number and p.phonenumber combination.
0
 
dba123Author Commented:
well, here's one problem, when i run this

SELECT DISTINCT m.number, p.phonenumber FROM Master m (NOLOCK)
      INNER JOIN phones p ON p.number = m.number
      WHERE      p.phonetypeID = 1
                  AND (p.good <> NULL OR p.good <> 0)
                  AND LEN(p.phonenumber) = 10

I am still getting duplicate m.numbers sometimes
0
 
dba123Author Commented:
example data returned by the inner select statement:

671644      9164133598
674055      8344324322
708037      3238322022
708039      4153469206
708040      8417446474< --
708040      8317863663< --
708042      3823723348
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
sudheeshthegreatCommented:
Doesn't matter how many records the inner query returns. The filter criteria is applied when you make the join and only those records in the table 'Master' will get updated which satisfy this filter criteria.
This is evident from the first part of the SQL statement
UPDATE Master...
which means only the table 'Master' will get updated.
0
 
dba123Author Commented:
Yes, I know that.  Why is distinct returning dups?
0
 
dba123Author Commented:
my real problem I realized is the dups now, can I get help with this?  thanks!
0
 
Brian CroweDatabase AdministratorCommented:
DISTINCT is combining the m.number and p.phonenumber to determine distinctness so when comparing records if either value is different then the record is distinct.  You will need to define which phonenumber you want to keep.  Without knowing more about your phonenumber table it's hard to give much direction on this.
0
 
Brian CroweDatabase AdministratorCommented:
FYI you can replace

AND (p.good <> NULL OR p.good <> 0)

with

AND ISNULL(p.good, 0) <> 0
0
 
dba123Author Commented:
>>>You will need to define which phonenumber you want to keep.

can you clarify what you mean by this
0
 
dba123Author Commented:
thanks for the tip on that p.good!
0
 
dba123Author Commented:
I think I actually want this instead:

SELECT DISTINCT p.number, p.phonenumber FROM Master m (NOLOCK)
      INNER JOIN phones p ON p.number = m.number
      WHERE      p.phonetypeID > 1
                  AND ISNULL(p.good, 0) <> 0
                  AND LEN(p.phonenumber) = 10

I changed m.number to p.number since I want to join on the p.numbers found right? the goal here is to update m.number with p.phonenumber if the fields in phones meet the certain criteria in the sub select.

That change to p.number gave me only around 13 rows this time.  I still get dups on p.number though
0
 
dba123Author Commented:
actually, what the heck am I doing in my second select, I don't need master at all!

UPDATE Master
      SET master.workphone = list.phonenumber
FROM MASTER m
INNER JOIN
      (SELECT DISTINCT p.number, p.phonenumber FROM phones p
      WHERE      p.phonetypeID > 1
                  AND ISNULL(p.good, 0) <> 0
                  AND LEN(p.phonenumber) = 10
      ) AS list ON m.Number = list.Number
WHERE m.workphone IS NULL OR m.workphone = ''
0
 
dba123Author Commented:
SELECT DISTINCT p.number, p.phonenumber FROM phones p
      WHERE      p.phonetypeID > 1
                  AND ISNULL(p.good, 0) <> 0
                  AND LEN(p.phonenumber) = 10

produces 215 still....actually.

now back to the dups
0
 
dba123Author Commented:
>>distinct would apply on both the columns in the select list. so, here it would return a distinct set of m.number and p.phonenumber combination.

ahhhh!  I see now!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.