David Freeman writes "I have a table that has a listing of businesses. Some owners have more than one business. The common thread is the phone number. We assign the businesses to muliple people to call, but don't want multiple people calling the same owner.
Therefore I need to develop a query that will identify the value in the assigned field (an integer) and for every record that has the same phone number (varchar 20) update the assigned value to that of the first occurrence:
David - how do you know which assigned is the one you want to go with - for example why 29, and why not 34 - if we just always go for the lowest assigned number (in this case 29) is that a problem?
Also, do you want an (roughly) equal number of different phone numbers per assigned?
-- I hope that when I die someone will say of me "That guy sure owed me a lot of money"
I need to make the assigned value of the second two records equal to 29
Have thousands of these.
SQL Server 2000, Windows Server 2003"
Assuming the Minimum Assigned Value will do...
UPDATE M
SET Assigned = MinAssigned
FROM MyTable M
INNER JOIN (
SELECT PrimaryPhone, MIN(Assigned) As MinAssigned
FROM MyTable
GROUP BY PrimaryPhone
) MiniMe
ON MiniMe.PrimaryPhone = M.PrimaryPhone
Modifying Records based on one duplicate field
Posted - 05/09/2005 : 08:24:24
