Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Update/Insert Records In Table Based Off Of Match In Another Table

Posted on 2011-02-21
5
Medium Priority
?
496 Views
Last Modified: 2012-06-22
I have two tables that both contain customer information. Lets call one table "Person" and the other table "DemographicData". The Person table contains person's names, and contact information. The DemographicData table has historical data about a person including their names and contact info. When a person updates their contact information, a new record is added to the DemographicData table with the date that it was changed. So one person may have multiple records in the DemographicData table. This allows us to track the person's history.

What I want to do is update the Person table with the most up-to-date contact information for that person from the DemographicData table by matching on FirstName, LastName, and DOB. If a match is not found in the DemographicData table then I want to add a new record to Person with the most up to date contact info for that person from DemographicData. So I beleive that I want to perform some type of "Upsert" like query, but I do not know how to do it with out using a cursor to go through each record in DemograhicData one at a time.

I have really simplified the enviroment that I am working with, but hopefully this is enough information. Thanks for any help.
0
Comment
Question by:MattStewart01
[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
  • 2
5 Comments
 
LVL 17

Expert Comment

by:dbaSQL
ID: 34946483
I think this would be pretty easily done.  Update if exists, otherwise insert. Can you provide example data from both the person and demographicdata tables?
0
 

Author Comment

by:MattStewart01
ID: 34952552
Person Table
--------------------------------
PersonID      FirstName      LastName      DOB                  Address
1                  Matt            Smith            1/13/1980      123 Main St.
2                  Eric            Brown            2/11/1970      355 Bay St.
3                John            Stewart            7/1/1984      1453 City Ln.
4                  Sam             Smith            6/1/1982      463 Oak St.


DemographicData Table
--------------------------------
FileDate      FirstName      LastName      DOB                  Address
1/5/2011      Matt            Smith            1/13/1980      123 Green St.
1/5/2011      Emily            Thomas            6/27/1974      1654 Old West St.
1/5/2011      Thomas            Green            11/4/1990      633 King St.
9/1/2010      Eric            Brown            2/11/1970      355 Bay St.
9/1/2010    John            Stewart            7/1/1984      1453 City Ln.
9/1/2010      Sam             Smith            6/1/1982      463 Oak St.
9/1/2010      Matt            Smith            1/13/1980      123 Main St.


So in this example that I just made up there a list of persons in the Person table. Each one of these persons could have more than one record in the DemographicData table. So I want to loop through each row in the DemographicData table and look for a match in the Person table. So the query should see that Matt Smith has a match in the Person table, but it also sees that there is more than one record for that person in the DemographicData table. So it will look for the most recent FileDate for Matt Smith and update his Address since it has changed. The query would also add any new persons that it finds, so in this case it would add Emily Thomas and Thomas Green. At a future date I will be adding a processed flag so that DemographicData records will only be processed once, but at this point we are starting from scratch going back through serveal years worth of person files so I have to go through the entire DemographicData table.

Again this is overly simplified, but it should give you a good idea of what I am trying to do. I can do it with a cursor, but it would litterally take days to run.
0
 
LVL 17

Accepted Solution

by:
dbaSQL earned 2000 total points
ID: 34957601
My apologies for the delay, MattStewart01.  Bit of a family emergency.  Just wanted to add this in real quickly.  You can do this as-is, by passing in your @firstname and @lastname, or you could cursor through the FirstName and LastName values within the DemographicData table.  


I just did it with Matt Smith, and the Address was updated correctly.  Try it with Emily Thomas, and see if you get that insert into the Person table.
DECLARE @filedate date,
		@firstname varchar(25),
		@lastname varchar(25)
		
SET @firstname = 'Matt'
SET @lastname = 'Smith'		
 
IF EXISTS(
SELECT 1 FROM dbo.Person p JOIN dbo.DemographicData d
  ON p.FirstName = d.FirstName
  AND p.LastName = d.LastName
  WHERE p.FirstName = @firstname
  AND p.LastName  = @lastname
  )	
  BEGIN
	SELECT @filedate = MAX(FileDate) FROM dbo.DemographicData d WHERE d.FirstName = @firstname AND d.LastName = @lastname
	
	UPDATE d
	SET [Address] = p.Address
	FROM dbo.Person p JOIN dbo.DemographicData d
      ON p.FirstName = d.FirstName
      AND p.LastName = d.LastName
	WHERE d.Address <> p.Address
	AND d.FileDate = @filedate	
  END
  ELSE
  BEGIN
	IF NOT EXISTS(
	SELECT 1 FROM dbo.Person p
	WHERE p.FirstName = @firstname
	AND p.LastName = @lastname
	)
	BEGIN
		INSERT dbo.Person (FirstName, LastName, DOB, [Address])
		SELECT FirstName,LastName,DOB,[Address]
		FROM dbo.DemographicData 
    END
  END

Open in new window

0
 

Author Comment

by:MattStewart01
ID: 34957619
This actually works perfectly. Thanks for all of the help!
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 34957631
Excellent.  Glad to have helped.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

660 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