Solved

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

Posted on 2014-02-05
6
255 Views
Last Modified: 2014-02-12
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.
0
Comment
Question by:garberj76
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39836575
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
 

Author Comment

by:garberj76
ID: 39836687
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
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39836903
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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

Author Comment

by:garberj76
ID: 39836981
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
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 39837110
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
 

Author Closing Comment

by:garberj76
ID: 39853932
Thanks for your help on this!
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question