[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

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 = ''
0
dba123
Asked:
dba123
  • 10
  • 2
  • 2
1 Solution
 
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
 
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
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
 
sudheeshthegreatCommented:
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
 
Brian CroweCommented:
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 CroweCommented:
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 10
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now