Its really a problem on INSERT statement

[katharnakh]

mysql> insert into fund_history select * from temp_fund_history 
where data_date='2006-06-30' and index_num between 1 and 10;

Query OK, 0 rows affected (0.03 sec)
Records: 10  Duplicates: 10  Warnings: 0

I am really wondering why this INSERT statement is not inserting 10 records. Its saying 10 Duplicates: 10, and there is no records from index_num from 1 to 10 in fund_history table.

Can any one please help me to know the reason/cause and solution for this problem.

Thanks,
Kath

[pty]

mysql> insert into fund_history select * from temp_fund_history 
where data_date='2006-06-30' and index_num between 1 and 10;

Query OK, 0 rows affected (0.03 sec)
Records: 10  Duplicates: 10  Warnings: 0

I am really wondering why this INSERT statement is not inserting 10 records. Its saying 10 Duplicates: 10, and there is no records from index_num from 1 to 10 in fund_history table.

Can any one please help me to know the reason/cause and solution for this problem.

Thanks,
Kath

do any of your fields have a unique constraint?

yes. thought so.

[katharnakh]

Oh, yes thats true, one of the is primay field, and i'm trying to insert duplicate record.
Thanks a lot.

But I suprised why MySQL not promted/informed me about the duplicate record. I think it should, right?

Please feel free to comment on this.

Regards,
kath :)

[pty]

To be honest I'm no MySQL expert (PostgreSQL fan) but it seems that MySQL has just skipped the inserts that would cause problems:

Query OK, 0 rows affected (0.03 sec)
Records: 10  [b]Duplicates: 10[/b]  Warnings: 0

In PostgreSQL you'd get:

ERROR:  duplicate key violates unique constraint "constr_active_level_type"