dba123
asked on
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 = ''
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 = ''
ASKER
example data returned by the inner select statement:
671644 9164133598
674055 8344324322
708037 3238322022
708039 4153469206
708040 8417446474< --
708040 8317863663< --
708042 3823723348
671644 9164133598
674055 8344324322
708037 3238322022
708039 4153469206
708040 8417446474< --
708040 8317863663< --
708042 3823723348
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.
This is evident from the first part of the SQL statement
UPDATE Master...
which means only the table 'Master' will get updated.
ASKER
Yes, I know that. Why is distinct returning dups?
ASKER
my real problem I realized is the dups now, can I get help with this? thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
FYI you can replace
AND (p.good <> NULL OR p.good <> 0)
with
AND ISNULL(p.good, 0) <> 0
AND (p.good <> NULL OR p.good <> 0)
with
AND ISNULL(p.good, 0) <> 0
ASKER
>>>You will need to define which phonenumber you want to keep.
can you clarify what you mean by this
can you clarify what you mean by this
ASKER
thanks for the tip on that p.good!
ASKER
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
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
ASKER
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 = ''
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 = ''
ASKER
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
WHERE p.phonetypeID > 1
AND ISNULL(p.good, 0) <> 0
AND LEN(p.phonenumber) = 10
produces 215 still....actually.
now back to the dups
ASKER
>>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!
ahhhh! I see now!
ASKER
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