SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Modifying Records based on one duplicate field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 05/09/2005 :  08:24:24  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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:

Assigned PrimaryPhone
29 800-337-9090
34 800-337-9090
65 800-337-9090

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"

rrb
SQLTeam Poet Laureate

Australia
1479 Posts

Posted - 05/09/2005 :  20:12:10  Show Profile  Reply with Quote
Question

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"

Edited by - rrb on 05/09/2005 20:12:57
Go to Top of Page

SamC
White Water Yakist

USA
3460 Posts

Posted - 05/09/2005 :  23:38:59  Show Profile  Reply with Quote
quote:
Originally posted by AskSQLTeam


Assigned PrimaryPhone
29 800-337-9090
34 800-337-9090
65 800-337-9090

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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000