Query for Bulk update in Mysql

Question

Consider the following table(T) in mysql.

Suppose I want to update "c1" of the row with id=2. I can do the following.

UPDATE T set col1=20 where id=1;

Suppose I want bulk update.

INSERT INTO T(id,c1) VALUES (1,20),(2,40) ON DUPLICATE KEY UPDATE c1=VALUES(c1);

But this will give you an error if default value for "c2" is not set in the SCHEMA.

Is there another way to achieve the task?

@fancyPants First it try to check if all the attributes are present before it check whether the id is duplicate.That is why I am getting the error. But I don't know why it is not giving error for you. are you using mysql? — Avinash142857, Dec 04 '17 at 09:46
Nevermind, didn't see that the error is actually for column c2. — fancyPants, Dec 04 '17 at 10:05

fancyPants · Answer 1 · 2017-12-04T10:27:06.013

1

The error happens when you INSERT a value for the first time, meaning there's no duplicate key to update. Your c2 column is not nullable and has no default value. To fix this either

make c2 nullable or
provide a default value for c2 or
provide a value for c2 in your insert statement

When you don't want to insert into the table, insert the values into a temporary table, then use an update statement and join the two tables.

edited Dec 04 '17 at 10:27

answered Dec 04 '17 at 10:05

fancyPants

50,732
33
89
96

Is there any better way to do this(without using insert)? – Avinash142857 Dec 04 '17 at 10:18
Added another comment in the answer. – fancyPants Dec 04 '17 at 10:27
Actually I need to optimise the bulk update and I don't think this is the way to optimise it. – Avinash142857 Dec 04 '17 at 10:32
I need to do it with just a single sql query. No joins. – Avinash142857 Dec 04 '17 at 10:33
This would be a single query. `update a join b on a.id=b.id set a.whatever = b.whatever;` What's the problem with that? You won't optimize it any further, when you don't want to insert whatever data you provide. – fancyPants Dec 04 '17 at 11:08

Query for Bulk update in Mysql

1 Answers1