merge records issue 1

[myspiral]

I have a table with records that i need to merge
ie
FirstName LastName Phone Address
Ken Smith 555-0000
Ken Smith 123 4th st

BECOMES
FirstName LastName Phone Address
Ken Smith 555-0000 123 4th st


there are more field than this, but this is a good example.
Sometimes 1 record may be missing a phone and the other has it, sometimes its the address. Any advice?

 

[cranebill]

What if you have...

FirstName LastName Phone Address
Ken Smith 555-0000 123 4th st
Ken Smith 777-0000 456 5th st

Which record would you keep...

or are you for sure you will only have 2 instances of a name and one field will be populated in each table?

 

[Golom]

I have a user interface and some rather complex code that does that. Before we get to that however, what would you expect to happen if you had
[tt]
FirstName LastName Phone Address
Ken Smith 555-0000 567 Main St
Ken Smith 555-1111 123 4th st
Ken Smith 123 4th st
Ken Smith 555-0000
[/tt]
It's easy enough to write UPDATE statements that match two records and plug values into NULL fields but the situation with more than two records and/or non-null but different field values is trickier.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[myspiral]

good question should have put that in my initial post.
that set I would keep both as the records are not really duplicates. I only want to merge where one field is null and the other is not.

 

[Golom]

Then something of the form

UPDATE myTable T1 INNER JOIN myTable T2
       ON T1.FirstName = T2.FirstName AND 
          T1.LastName  = T2.LastName

Set T2.Phone = T1.Phone

Where T2.Phone IS NULL 
  AND T1.Phone IS NOT NULL

should update your Phone numbers.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[cranebill]

Ya beat me to it Golom

 

[myspiral]

thanks Golom exactly what I needed!

 

[PHV]

Anyway, have a look here:

http://r937.com/relational.html