Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-02-05
6
Medium Priority
?
258 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 1500 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

719 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