Hello masters and students, I have a weird issue and Im nearly to hit my head on the wall Well... I have 2 tables. Lets say: TABLE B: | Field | Type | Null | Key | Default | Extra | x' ... ... PRI ... z' ... ... ... TABLE B: | Field | Type | Null | Key | Default | Extra | x ... ... PRI ... z ... ... ... y int(11) ... PRI ... auto-increment Note: I use both 2 fields as primary keys in Table B. Problem: For most rows, the below query succeeds: INSERT INTO tableB (x,z) SELECT x',z' FROM tableA where ... <condition> Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 I see that the specific row from tableB is copied to tableA and the y value is incremented by one... BUT... for a specific value, it fails to increment the y field: INSERT INTO tableB (x,z) SELECT x',z' FROM tableA where ... <condition> ERROR 1062 (23000): Duplicate entry '.......' for key 1 It seems that if fails to increment the y value and when it inserts it into the tableB, it already has a duplicate with same primary key values(x and y) and if fails. As I run a script with many many values, its not easy for me to define each time a specific value for auto_increment. Any ideas? Thanks
maybe INSERT INTO tableB (x,z, y) SELECT x',z', null as y FROM ... and maybe you should remove primary key from x-field