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

Question

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.

I don't think you can have a single SQL statement which updates or inserts. You'd probably be better off inserting all those whose ids are null and updating afterwards all those whose ids are not null. — Neil, Jan 24 '18 at 11:16
What happens if you insert a row where the `id` value is not NULL _and_ it doesn't already exist in the table? Would that also be an insert? — Tim Biegeleisen, Jan 24 '18 at 11:18
Possible duplicate : https://stackoverflow.com/questions/6030071/mysql-table-insert-if-not-exist-otherwise-update — Hamza Abdaoui, Jan 24 '18 at 11:19
Possible duplicate of [MySql Table Insert if not exist otherwise update](https://stackoverflow.com/questions/6030071/mysql-table-insert-if-not-exist-otherwise-update) — Ankur Sinha, Jan 24 '18 at 11:20
@TimBiegeleisen I would actually, want to ignore if that is the case, because I think in that case, the row would have been deleted in the time since I received the id to update. — Kasper, Jan 24 '18 at 11:21

ild flue · Accepted Answer · 2018-01-24T12:10:56.370

3

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;

edited Jan 24 '18 at 12:10

answered Jan 24 '18 at 11:18

ild flue

1,323
8
9

Thanks for the answer. This answer has 2 things, I don't like. The ids are not incrementing normally again. It now goes from 3,4 to 8,9, to 13,14. The other thing is that, it also insert the id, if it doesn't exists. I want to ignore if there is nothing to update. – Kasper Jan 24 '18 at 11:31
You can compose the `VALUES(1, "body text")` by a loop to get values like `($id, $value)`, `$id` can be `NULL` or the numbers as you mentioned. Would be possible? – ild flue Jan 24 '18 at 11:36
My main problem is that it inserts if the id doesn't exists. I only want to insert if the id is null. It now also inserts if the id =3 and the id 3 doesn't exists in the table. – Kasper Jan 24 '18 at 11:44
How about: `if(is_null($id)) { INSERT QUERY } else { SELECT ... WHERE id=$id; if (affected_rows > 0) { UPDATE QUERY}}` ? – ild flue Jan 24 '18 at 11:51
Maybe better: `if(is_null($id)) { INSERT GROUP } else { UPDATE GROUP}` ? – ild flue Jan 24 '18 at 12:03

score 1 · Answer 2 · answered Jan 24 '18 at 11:18

1

Try this:

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

answered Jan 24 '18 at 11:18

Kannan K

4,411
1
11
25

Thanks for the answer. This answer has 2 things, I don't like. The ids are not incrementing normally again. It now goes from 3,4 to 8,9, to 13,14. The other thing is that, it also insert the id, if it doesn't exists. I want to ignore if there is nothing to update. – Kasper Jan 24 '18 at 11:31

score 1 · Answer 3 · edited Jan 24 '18 at 11:44

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!

score 0 · Answer 4 · answered Jan 24 '18 at 11:18

0

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');

answered Jan 24 '18 at 11:18

Farhan Qasim

990
5
18

Thanks for the answer. This answer has 2 things, I don't like. The ids are not incrementing normally again. It now goes from 3,4 to 8,9, to 13,14. The other thing is that, it also insert the id, if it doesn't exists. I want to ignore if there is nothing to update. – Kasper Jan 24 '18 at 11:27

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

4 Answers4