CODEWITHSUNDEEP
mysql
bschaeffer
bschaeffer

Reputation: 2904

MySQL: mass update unique records

Say I have a .yml file with some data in that looks like this:

- id: 1
  cool_data: 124
- id: 2
  cool_data: 178
- id: 3
  cool_data: 101
...

Considering all the id values represent existing records in the database, how do I mass import the information (possibly over a couple of statements), rather issue an UPDATE query for each data point?

Upvotes: 0

Views: 74

Answers (1)

Mike Brant
Mike Brant

Reputation: 71384

You should be able to use INSERT ... ON DUPLICATE KEY UPDATE syntax in combination with VALUES() function:

INSERT INTO table_name (id, cool_data)
VALUES
  (1, 124),
  (2, 178),
  (3, 101),
  ...
ON DUPLICATE KEY
UPDATE cool_data = VALUES(cool_data)

Of course this will actually INSERT if that primary key value does not exist.

Upvotes: 1

Related Questions