complete records with data from similar records

Question

complete records with data from similar records

theflyguy

SSC Rookie

Points: 31
More actions
April 29, 2008 at 5:55 am

#184929

This may be a common task, but I never had to do something like that, so I need your help. One of my Tables is filled from different sources (Import Tasks), where one source gives me a complete set of data (i.e. customer name and customers office location) and the other one only partial data (customer name in this case). A Table could look like that
lastname, firstname, office
------------------------------------
Rogers, Ginger, Brussels
Smith, John, Munich
Rogers, Ginger, null
Smith, John, null
Now I need a query to add the missing office entries to the records where this field is null, retrieving this information from the records of the same person where this field is filled.
How can I do that?
Kind Regards
Peter

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

[font="Verdana"]

Considering you have complete set of data into TableA and TableB have incompletedata. Here you need to update the TableB according to the TableB, right?

try this ...

Update TableB

Set TableB.Office = TableA.Office

From TableA Inner Join TableB On TableA.FirstName = TableB.FirstName

And TableA.LastName = TableB.LarstName

confirm on this.

Mahesh

[/font]

MH-09-AM-8694

Jeff Moden SSC Guru Points: 1004355 More actions · Answer 2

If the data is all in one table, there's no need to make it in two separate tables... you can make it appear as if you did that with table aliases... thusly...

--===== Create a table to demo with, this is NOT part of the solution

DECLARE @DemoTable TABLE

(LastName VARCHAR(20),

FirstName VARCHAR(20),

Office VARCHAR(20))

--===== Populate the demo table with test data, this is NOT part of the solution

INSERT INTO @DemoTable

(LastName, FirstName, Office)

SELECT 'Rogers','Ginger','Brussels' UNION ALL

SELECT 'Smith','John','Munich' UNION ALL

SELECT 'Rogers','Ginger','Brussels' UNION ALL

SELECT 'Smith','John','Munich' UNION ALL

SELECT 'Rogers','Ginger',NULL UNION ALL

SELECT 'Smith','John','' UNION ALL

SELECT 'Rogers','Ginger','' UNION ALL

SELECT 'Smith','John',NULL UNION ALL

SELECT 'Rogers','Ginger',NULL UNION ALL

SELECT 'Smith','John',NULL

--===== Solve the problem

UPDATE dt2

SET dt2.Office = dt1.Office

FROM @DemoTable dt1

INNER JOIN

@DemoTable dt2

ON dt1.FirstName = dt2.FirstName

AND dt1.LastName = dt2.LastName

WHERE dt1.Office > '' --Faster than IS NOT NULL and catches blanks, too

AND (dt2.Office IS NULL OR dt2.Office = '')

--===== Show that it worked

SELECT * FROM @DemoTable

The caveat is that you may have to ensure that the filled in occurances of FirstName, LastName all have the same Office name... you can obviously have duplicates of all 3 columns but if Ginger works in more than one office, you'll get unpredictable results no matter what you do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

theflyguy SSC Rookie Points: 31 More actions · Answer 3

Sorry for the late response, I was struggling with another issue in our production Database today, so I haven't found the time to come back to this forum yet.

Thank you very much for this helpfull example. I'll try it immediately tomorrow.

Regards

Peter