insert non duplicate data 1

[benaround]

Sorry, I'm having a brain freeze day, in doing a
insert into tbl1 (field1, field2) select fielda, fieldb from tbl2 I dont want duplicate data for field2.
Please help, thanks

 

[ClaireHCM]

insert into tbl1 (field1, field2) select distinct fielda, fieldb from tbl2

 

[benaround]

I have tried distinct and it is not stopping the dup records. I'm not sure why.

 

[ClaireHCM]

Do you want only certain columns to be unique? Say only field1, in this case, for Field2 you need to speficy either max/min....

 

[RileyCat]

benaround,

You have a couple of options. But it totally depends on what you're trying to do ...

What are you intending to do if Field2 already exists in Tbl1 ...

1) insert Field1 data with a NULL in field2,
2) abort insert for the given field1/field2 pairing,
3) something else ...

 

[benaround]

Briefly, if I have a data input file and it has duplicate records. I'm want to add only 1 of these records to another table for further processing. They are unique in the telephone number field of the input data.

 

[RileyCat]

Makes sense! So, if I get you right, you've got something like this ...

Tbl2 Field1 Field2
==== ====== ======
"John Doe" "123-456-7890"
"John Doe" "123-456-7890"
"Jane Doe" "123-456-7890"

you want to insert into Tbl1 the following ...

Tbl1 Field1 Field2
==== ====== ======
"John Doe" "123-456-7890"
"Jane Doe" "123-456-7890"

If so, using distinct, group by, or Max/Min will not work as you would first think.

You'd be better off using some join coupled with exists, not in, or other exclusitory logic.