INSERT ... SELECT Syntax doesn't insert all rows

Question

When I run this query it returns 6,338 total rows.

  SELECT updated.playerId
  FROM updated WHERE updated.updated = 0;

However, when I run this query it only inserts 2,771 total rows, when it should insert all 6,338.

  INSERT IGNORE INTO playerold (playerID)
  SELECT updated.playerId
  FROM updated WHERE updated.updated = 0;

Am I missing something here?

I get this error: Duplicate entry '0' for key 'playerID'. But there is no playerID that equals 0 in either table. — Koala, Jul 04 '14 at 10:14
@user1626410, Because you're trying to add already been added row. — Orel Eraki, Jul 04 '14 at 10:14
Well all those rows that generate an error won't be inserted when you use the `IGNORE` keyword. — , Jul 04 '14 at 10:16
And how many rows do you get if you do `select u.playerId from updated u where u.updated = 0 and exists (select null from playerold where playerID = u.playerID)` ? Maybe 6638 - 2771 => 3867 ? — Raphaël Althaus, Jul 04 '14 at 10:16
[Found interesting link on SE for INSERT IGNORE](http://stackoverflow.com/a/7853634/2679536) — Shaunak Shukla, Jul 04 '14 at 10:24
And what do you really want : be able to have multiple times the same playerId in oldPlayer table, or not ? If yes, you'll have to change this table's structure. — Raphaël Althaus, Jul 04 '14 at 10:25

Ivan Rodriguez · Answer 1 · 2014-07-04T10:20:48.657

1

If you see the error you need to remove the word IGNORE your sql statement. I think you have a different constraint in another table. Check table constraints.

You can try CREATE TABLE playerold SELECT playerID FROM updated WHERE updated.updated = 0;

edited Jul 04 '14 at 10:20

answered Jul 04 '14 at 10:14

Ivan Rodriguez

426
4
14

score 1 · Accepted Answer · answered Jul 04 '14 at 12:10

1 Remove the unique constraint available in playerold table if you want all the records and use the below query:

 INSERT INTO playerold (playerID)
 SELECT updated.playerId
 FROM updated WHERE updated.updated = 0;

Or,

2 Try:

 INSERT INTO playerold (playerID) VALUES (SELECT updated.playerId
 FROM updated WHERE updated.updated = 0) ON DUPLICATE KEY UPDATE playerID=playerID

INSERT ... SELECT Syntax doesn't insert all rows

2 Answers2