insert update multiple rows mysql

Question

I need to add multiple records to a mysql database. I tried with multiple queries and its working fine, but not efficient. So I tried it with just one query like below,

INSERT INTO data (block, length, width, rows) VALUES
    ("BlockA", "200", "10", "20"),
    ("BlockB", "330", "8", "24"),
    ("BlockC", "430", "7", "36")
ON DUPLICATE KEY UPDATE 
    block=VALUES(block),
    length=VALUES(length),
    width=VALUES(width),
    rows=VALUES(rows)

But it always update the table (columns are block_id, block, length, width, rows). Should I do any changes on the query with adding block_id also. block_id is the primary key. Any help would be appreciated.

Answer 1

I've run your query without any problem, are you sure you don't have other keys defined with the data table ? And also make sure you have 'auto increment' set for the id field. without auto_increment, the query always update existing row

***** Updated **********

Sorry I've mistaken your questions. Yes, with only one auto_increment key, you query will always insert new rows instead of updating existing one ( because the primary key is the only way to detect 'existing' / duplication ), since the key is auto_increment, there's never a duplication if the primary key is not given in the insert query.

I think what you want to achieve is different, you might want to set up composite unique key on all fields (i.e. block, field, width, rows )

By the way, i've set up a SQL fiddle for you. http://sqlfiddle.com/#!2/e7216/1

The syntax to add the unique key:

CREATE TABLE `data` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`block` varchar(10) DEFAULT NULL,
`length` int(11) DEFAULT NULL,
`width` int(11) DEFAULT NULL,
`rows` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniqueme` (`block`,`length`,`width`,`rows`)
 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;