Combine addresses missing Unit/Suite #
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) &gt; 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