CODEWITHSUNDEEP
mysql
Kasper
Kasper

Reputation: 13592

Mysql batch insert or update, insert if id is null otherwise update

Say I have the table:

CREATE TABLE texts
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    body TEXT
);

I want to update a couple of ids, and also insert some new ones. I want to insert if the id is null, and otherwise, I want to update. So I want something like this:

INSERT INTO OR UPDATE 
  texts (id, body)
VALUES
  (NULL, 'create new row'),
  (NULL, 'create other new row'),
  (1, 'update id 1'),
  (2, 'update id 2'),
  (3, 'update id 3');

If the matching id, doesn't exist, I want to ignore the update, because then the row is apperently deleted in the meantime.

Upvotes: 0

Views: 1123

Answers (4)

ild flue
ild flue

Reputation: 1361

There is a statement INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO texts (id, body) VALUES(1, "body text") ON DUPLICATE KEY UPDATE    
body="body text"

If you want to skip the ID not null not exists in the table, maybe you can try this

INSERT INTO
  texts (id, body)
VALUES
  (NULL, 'create new row'),
  (NULL, 'create other new row');

UPDATE texts SET
`body` = CASE 
WHEN `id` = 1 THEN 'update id 1'
WHEN `id` = 2 THEN 'update id 2'
WHEN `id` = 3 THEN 'update id 3'
ELSE `body` END

WHERE `id`=1 OR `id`=2 OR `id`=3;

Upvotes: 3

nahuelhds
nahuelhds

Reputation: 532

If performing two queries is not a problem you could UPDATE and INSERT separately. First, the UPDATE query:

UPDATE my_table SET field1 = 
  (CASE id 
     WHEN 1 THEN 'value1'
     WHEN 2 THEN 'value2'
     WHEN 3 THEN 'value3'
  END),
  field2 = 
  (CASE id 
     WHEN 1 THEN 'value4'
     WHEN 2 THEN 'value5'
     WHEN 3 THEN 'value6'
  END)
WHERE id IN(1,2,3)

On the insert side

INSERT INTO my_table(id, field1, field2) VALUES
  (NULL, 'value7', 'value8'),
  (NULL, 'value9', 'value10'),
  (NULL, 'value11', 'value12')
;

That would result in something like:

id | field1  | field 2
-----------------------
1  | value1  | value4
2  | value2  | value5
3  | value3  | value6
4  | value7  | value8
5  | value9  | value10
6  | value11 | value12

TEST IT YOURSELF

You could try yourself this by creating the schema

 CREATE TABLE `my_db`.`my_table` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `field1` VARCHAR(45) NULL,
  `field2` VARCHAR(45) NULL,
  PRIMARY KEY (`id`)
 );

And some initial data so you can check the updates

INSERT INTO `my_db`.`my_table` (`field1`, `field2`) VALUES ('value', 'anotherValue');
INSERT INTO `my_db`.`my_table` (`field1`, `field2`) VALUES ('moreValue', 'justAnother');
INSERT INTO `my_db`.`my_table` (`field1`, `field2`) VALUES ('moreAndMore', 'valuesEverywhere');

Then just perform the queries to see with your own eyes.

Hope it helps!

Upvotes: 1

Kannan K
Kannan K

Reputation: 4461

Try this:

INSERT INTO texts(id, body) VALUES(1, "20") ON DUPLICATE KEY UPDATE body=20

Upvotes: 1

Farhan Qasim
Farhan Qasim

Reputation: 990

REPLACE INTO 
texts (id, body)
VALUES
(NULL, 'create new row'),
(NULL, 'create other new row'),
(1, 'update id 1'),
(2, 'update id 2'),
(3, 'update id 3');

Upvotes: 0

Related Questions