Combine addresses missing Unit/Suite #

Question

I have a list of addresses which need to be updated in SQL Server. How can I go about adding the Suite # to the addresses which are missing it?

Address
1 Burton Hills Blvd, Suite 250
1 Burton Hills Blvd
1 Burton Hills Blvd
1 Burton Hills Blvd
1 Burton Hills Blvd
1 Burton Hills Blvd
1 Ford Pl
1 Ford Pl, Suite 5B
1 Kish Dr
1 Park Center Dr, Suite 11
1 Park Center Dr

Answer 1

This is not a clean problem. The following should work assuming that there will not be multiple street addresses with different suite numbers. Also the data will have to be formatted with the comma and space before the Suite. I added a duplicate record (1 Burton Hills...) for testing the group by logic.

Create Table Addresses (Address Varchar(8000))

Insert Addresses (Address) Values ('1 Burton Hills Blvd, Suite 250')
Insert Addresses (Address) Values ('1 Burton Hills Blvd, Suite 250')
Insert Addresses (Address) Values ('1 Burton Hills Blvd')
Insert Addresses (Address) Values ('1 Burton Hills Blvd')
Insert Addresses (Address) Values ('1 Burton Hills Blvd')
Insert Addresses (Address) Values ('1 Burton Hills Blvd')
Insert Addresses (Address) Values ('1 Burton Hills Blvd')
Insert Addresses (Address) Values ('1 Ford Pl')
Insert Addresses (Address) Values ('1 Ford Pl, Suite 5B')
Insert Addresses (Address) Values ('1 Kish Dr')
Insert Addresses (Address) Values ('1 Park Center Dr, Suite 11')
Insert Addresses (Address) Values ('1 Park Center Dr')

;
With cteWithSuite As
    (
    Select Substring(Address, 1, CharIndex(', Suite', Address)-1) AddressRoot, Address
        From Addresses 
        Where CharIndex(', Suite', Address) > 0
        Group By Substring(Address, 1, CharIndex(', Suite', Address)-1), Address
    )
Update B Set B.Address = A.Address
    From cteWithSuite A
    Join (Select Address 
            From Addresses 
            Where CharIndex(', Suite', Address) = 0
        ) B On B.Address = A.AddressRoot