Solved

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

Posted on 2014-02-05
6
251 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 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

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 …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

809 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