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.

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 — David Lin
– David Lin, Commented Aug 12, 2013 at 9:44

David Lin · Accepted Answer · 2013-08-12 11:53:48Z

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;

edited Aug 12, 2013 at 11:53

answered Aug 12, 2013 at 9:47

David Lin

13.4k5 gold badges48 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Irawana Over a year ago

block_id is the only id is there. Also its auto increment. When I run this query again, its adding those data again instead of updating.

Collectives™ on Stack Overflow

insert update multiple rows mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related