Solved

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

Posted on 2014-02-05
6
249 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
  • 3
  • 3
6 Comments
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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
Comment Utility
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 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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 Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:garberj76
Comment Utility
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 39

Accepted Solution

by:
Kyle Abrahams earned 500 total points
Comment Utility
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
Comment Utility
Thanks for your help on this!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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 …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now