CODEWITHSUNDEEP
mysqlsql-update
Ray S.
Ray S.

Reputation: 1200

mysql update multiple id/value pairs with one statement

If I have a mysql table with the 2 columns:

ID   Date
1    2012-03-05
2    2012-02-21
3    2013-06-12
4    2011-01-15
5    2013-02-23

and I have an array of random updates such as

$arr = array('2'=>'2013-03-23','4'=>'2013-03-19','5'=>'2011-08-09');

(where the index is the ID and the value is the date)

is there a way to update the table with one statement?

the reason I am doing this, is because I need to make hundreds+ of changes and single updates would be alot of statements.

Upvotes: 0

Views: 1920

Answers (1)

Oswald
Oswald

Reputation: 31647

If ID is unique or a primary key, you can do

INSERT INTO `Table` (ID, `Date`)
VALUES ('2', '2013-03-23'), ('4', '2013-03-19'), ('5', '2011-08-09')
ON DUPLICATE KEY UPDATE `Date` = VALUES(`Date`)

Note that this might affect the auto increment value and it might insert new records into the table.

Upvotes: 3

Related Questions