CODEWITHSUNDEEP
mysqlinsertbulkinsert
tttt
tttt

Reputation: 13

MySQL bulk insert (ignoring primary key id, but on duplicate update another key)

On a bulk insert, how do I ignore a duplicate key update for primary key 'id' while only updating the entry for another key 'another_unique_id'?

INSERT INTO
  some_table (`id`, `another_unique_id`, `raw_data)
  VALUES     (5   ,  'ABCDEFG', 'blah')
ON DUPLICATE KEY UPDATE ...?

For example - If there's a record with id: 5, I don't want to update it. I'll just insert a new record.

If there's a record with 'ABCDEFG' in the 'another_unique_id' field, then I'd like to update that entry to 'blah'

Upvotes: 1

Views: 1329

Answers (2)

Natxo
Natxo

Reputation: 11

At least on mysql you can use:

INSERT IGNORE INTO
some_table (`id`, `another_unique_id`, `raw_data`)
VALUES     (5 ,  'ABCDEFG', 'blah')

If a field with id 5 exists it will do the same as an update, and if it doesn't exist, it will do the same as the regular insert.

Upvotes: 1

Andrej
Andrej

Reputation: 7504

I think you should pass 

INSERT INTO
some_table (`id`, `another_unique_id`, `raw_data)
VALUES     (NULL   ,  'ABCDEFG', 'blah')
ON DUPLICATE KEY UPDATE ...?

otherwise it doesn't make sense

Upvotes: 1

Related Questions