Replace a Record with the most frequently used column within a table

First post here, hoping I explain this correctly.  We are using a program that downloads names and addresses into a database that are related to a specific account.  These addresses came in all shapes and formats.  What we want to do is update the account's address listing with the most commonly used address for an entity which is used for all accounts.  I have a script that will replace an address while it looks for the most frequently used record with that entity's name in there, but I have to type in the entity name, e.g, LIKE '%DAIMLER%' for Daimler Chrysler.  That works, but I want it to look at the list in the current account and update the list with entities that are most frequenlty used across all accounts in the database.

Here is my current script:


UPDATE tblAccountAdd
SET tblAccountAdd.NameID =
      (SELECT NameID FROM tblNameMastersMasters
      WHERE NameID =
            (SELECT TOP 1 NameID
             FROM tblAccountAdd
            WHERE Name LIKE '%DAIMLER%'
            GROUP BY NameID
            ORDER BY Count(tblAccountAdd.AccountID) DESC))
FROM tblAccountAdd, tblNameMasters
WHERE tblAccountAdd.AccountID = 99999
AND tblNameMasters.NameID = tblAccountAdd.NameID
AND Name LIKE '%DAIMLER%'
AND NOT EXISTS (SELECT tblAccountAdd.NameID FROM tblAccountAdd, tblNameMasters
                        WHERE AccountID = 99999
                        AND tblNameMasters.NameID = tblAccountAdd.NameID
                        AND Name LIKE '%DAIMLER%')

I've tried replacing '%DAIMLER%' with (SELECT Name FROM tblAccountAdd WHERE AccountID = 99999)  but it returns an error because the subquery returns more than one value.
garberj76Asked:
Who is Participating?
 
Kyle AbrahamsSenior .Net DeveloperCommented:
try this:

select nameid, accountid, count(name) counts
into #temp2
from tblMasterNames
group by accountId, nameid
order by count(name) desc

-- get the max count per name id
select accountId, max(counts) counts
into #counts
from #temp2
group by accountId

--TEST WITH:
select
a.ACcountNameID oldId, x.NameId newID
from
tblAccountAdd a
join
(
select t.*
from #temp2 t
join #counts  c on t.accountId = c.accountId and t.counts = c.counts  
) x  on a.accountId = x.AccountId

-- THIS WILL UPDATE IF THE ABOVE SELECT IS ACCURATE:
/*
update a
set a.AccountNameId = x.NameId
from tblAccountAdd a
join
(
select t.*
from #temp2 t
join #counts  c on t.accountId = c.accountId and t.counts = c.counts  
) x  on a.accountId = x.AccountId*/
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
To fix your query:
UPDATE tblAccountAdd
SET tblAccountAdd.NameID =
      (SELECT Top 1 NameID FROM tblNameMastersMasters
      WHERE NameID =
            (SELECT TOP 1 NameID
             FROM tblAccountAdd
            WHERE Name LIKE '%DAIMLER%'
            GROUP BY NameID
            ORDER BY Count(tblAccountAdd.AccountID) DESC))
FROM tblAccountAdd, tblNameMasters
WHERE tblAccountAdd.AccountID = 99999
AND tblNameMasters.NameID = tblAccountAdd.NameID
AND Name LIKE '%DAIMLER%'
AND NOT EXISTS (SELECT tblAccountAdd.NameID FROM tblAccountAdd, tblNameMasters
                        WHERE AccountID = 99999
                        AND tblNameMasters.NameID = tblAccountAdd.NameID
                        AND Name LIKE '%DAIMLER%')
0
 
garberj76Author Commented:
Thanks, but what I need to do is to be able to replace '%DAIMLER%' with the current column of Name for tblAccountAdd.  Say for example in tblAccountAdd you have the following for an account ID:

tblAccountAdd.NmID  tblMasterNames.NameID    Name                                    AccountID
12345                              1                                              DAIMLER CHRYSLER            1590062
12346                              2                                               ALLY FINANCIAL                  1590062



This has been created from importing a list into this account.  However, we don't want these addresses, we want the version of the address that is used the most.   How can I compare the entity name in the current account to find the "correct" entity, or the version of that address that  is used the most for all accounts?  How I get the record in tblAccountAdd to be changed to the NameID in tblMasterNames (since this is the one that is used the most)?  That first column should be tblAccountAdd.NameID - had to abbreviate it so it would fit the width and not mess up the formatting.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Kyle AbrahamsSenior .Net DeveloperCommented:
can you give me just the relevant columns for the two tables and a sample and expect results:  (EG: before and after)  


In the case where you have 2 names that occur the same amount of times which would you choose?

Can it be in a stored proc?

I would probably do something like:
select 'A' name, 1 nameId into #temp
insert into #temp select 'B', 1
insert into #temp select 'A', 1
insert into #temp select 'C', 1
insert into #temp select 'D', 1
insert into #temp select 'D', 1

--let #temp represent your table
select * from #temp

-- get a list of counts by nameid and name
select name, nameid, count(name) counts
into #temp2
from #temp
group by name, nameid
order by count(name) desc

-- get the max count per name id
select nameid, max(counts) counts
into #counts
from #temp2
group by nameid

-- final query . . . you can do your updated based on this.
select t.* from #temp2 t
join #counts  c on t.Nameid = c.nameid and t.counts = c.counts

Open in new window

0
 
garberj76Author Commented:
In the event were 2 or more occur the same number of times it would probalby just be the TOP 1 of those.  Here is the before and after of what we want to do:

BEFORE:
tblAccountAdd.AccountNameID      tblAccountAdd.NameID
12345                                                 4444
12346                                                4445                        

AFTER:
tblAccountAdd.AccountNameID      tblAccountAdd.NameID
12345                              1      (this is the most commonly used version of the name from the count query)                   
12346                              2      (this is the most commonly used version of the name from the count query)
0
 
garberj76Author Commented:
Thanks for your help on this!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.