Insert into table remove duplicates 1

[smatthews]

I am trying to move some fields from one table to another using INSERT INTO. The problem is that if three particular fields (multi field key) in a record are duplicated I only want one of the records in the 'to' table.

Any suggestions as to the easiest way to accomplish this?

Thanks in advance for your help.
Smatthews

 

[Katy44]

I haven't tried it, but would 'insert top 1....'
work in the same way as 'select top 1' does?

 

[SQLSister]

The problem is how do you define which record if they have different data. First you have to set up the rules for that, then we can help you write the code to get you there.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[smatthews]

Thanks! How would I designate looking at only those three fields as dups?

 

[Katy44]

Sorry, I assumed you wouldn't care which record was 'saved'.
I'm actually working on a similar project so I'll keep an eye on this thread.

 

[smatthews]

That last question was in response to Katy44's response. Didn't see yours SQL Sister.. sorry.

O.k. the three fields: zip, latitude and longitude can be duplicated in the 'from' table. If that's the case I only want one of those records. It doesn't matter if the other fields in the record are duplicated.

 

[SQLSister]

See the problem isn;t those three fields, the problem is what data in the rest of the fields do you want? Example:

MYTable
Field1 Field2 Field3 field 4
1 John john@email.com lunch
1 John john@test.com dinner

Which of the two records should I preserve. One one last entered the one with the lunch value the one with the working email address? WHich one? Obviouls as you get more fields, this gets more complicated. If you are lucky enough to have a CreatedDate field, then you can use the first or last date as the record you want to pull. If you are really unlucky what you really need is in more than one record succh as the john@email.com and dinner.


Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[smatthews]

Thanks for responding. The other fields won't matter. They will already be identical based on the three fields I want to use to dedup by.

 

[SQLSister]

If you are certain they are identical then that is the easiest case.

Insert into NewTable (field1, field2, field3, field4)
Select field1, field2, Max(field3), Max (field4) from Oldtable Group By Field1, Field2

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[smatthews]

This seems to work. Thanks so much for your help!!!