But then I need the phonenumber in that subquery so that I can do list.phonenumber
ahh!
UPDATE Master
SET master.homephone = list.phonenumber
FROM MASTER m
INNER JOIN
(SELECT DISTINCT p.number, topphone.phonenumber
FROM phones p
INNER JOIN (SELECT top 1 phonenumber from phones) as topphone ON topphone.number = p.number
WHERE p.phonetypeID = 1
AND ISNULL(p.good, 0) <> 0
AND LEN(p.phonenumber) = 10
) AS list ON m.Number = list.Number
WHERE m.homephone IS NULL OR m.homephone = ''
Error:
Msg 156, Level 15, State 1, Procedure Skiaa, Line 66
Incorrect syntax near the keyword 'top'.
Msg 156, Level 15, State 1, Procedure Skiaa, Line 88
Incorrect syntax near the keyword 'top'.
I want to update m.homephone with the p.phonenumber based in the filtering critiria I have specified but this simple task is turning out to be a pain.
Without actually trying to recreate the tables on my local server, I'll try to answer this. This is assuming it's actually something wrong with evaluating the top function within your derived table structure.
Anyway, try something like this instead...
UPDATE Master
SET master.homephone = list.phonenumber
FROM MASTER m
INNER JOIN
(SELECT DISTINCT p.number, topphone.phonenumber
FROM phones p
WHEREp.phonetypeID = 1
AND ISNULL(p.good, 0) <> 0
AND LEN(p.phonenumber) = 10
AND p.number = (SELECT TOP 1 phonenumber from phones)
) AS list ON m.Number = list.Number
WHERE m.homephone IS NULL OR m.homephone = ''
OR...
DECLARE @.max_phone NVARCHAR(100)
SET @.max_phone = (SELECT TOP 1 phonenumber from phones)
UPDATE Master
SET master.homephone = list.phonenumber
FROM MASTER m
INNER JOIN
(SELECT DISTINCT p.number, topphone.phonenumber
FROM phones p
WHEREp.phonetypeID = 1
AND ISNULL(p.good, 0) <> 0
AND LEN(p.phonenumber) = 10
AND p.number = @.max_phone) AS list ON m.Number = list.Number
WHERE m.homephone IS NULL OR m.homephone = ''
