Avoid the duplicate primary keys

[neosonic]

Hi all, I need your help.

I have an insert query which insert a few rows into the table A.
TableA has two primary keys, which are fieldA and fieldB.

The SQL is written as follows:

INSERT INTO TableA (fieldA , fieldB, fieldC, fieldD)
SELECT DISTINCTROW TableB.fieldA, TableB.fieldB, TableB.fieldC, TableB.fieldD FROM TableB

The problem is that SELECT statement from TableB produces a duplicate combination of fieldA and fieldB. for example.

fieldA fieldB fieldC fieldD
1 1 3 4
1 1 2 5
1 2 5 6

Therefore I have an error when I try to run this insert statement.
What should I do to avoid this to happen?
I just want one row for any duplicate primary keys and I am not fussy. I just don't want this error to pop up.

the result that I desire will be something like

fieldA fieldB fieldC fieldD
1 1 3 4
1 2 5 6

Thank you so much, I really appreciate your help.

[urtrivedi]

How mysql server will know what to select out of multiple choices,
1134
1125

What is criteria of selection. what is the version of mssql

[pritaeas]

You can do a group by on fieldA and fieldB.

[BitBlt]

You could always add another key column (fieldC) to TableA. If you're getting duplicates, then your normalization is incomplete and your TableA can't uniquely represent the data you need to store.

Or you could cheat and create another column as primary key that is an identity column, and take fieldA and fieldB out of the primary key altogether.
(;) Being the DBA means never having to say you're sorry.)

[neosonic]

Hi... thanks for all the reply.
@Pritaeas thanks for your idea.

I can do it with following sql:

INSERT INTO TableA (fieldA , fieldB, fieldC, fieldD)
SELECT DISTINCTROW min(TableB.fieldA), min(TableB.fieldB), min(TableB.fieldC), min(TableB.fieldD) FROM TableB
GROUP BY TableB.fieldA, TableB.fieldB