CODEWITHSUNDEEP
mysqlbulkupdate
Avinash142857
Avinash142857

Reputation: 149

Query for Bulk update in Mysql

Consider the following table(T) in mysql.

id c1  c2
1  10  100
2  20  200
3  30  300

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?

Upvotes: 0

Views: 531

Answers (1)

fancyPants
fancyPants

Reputation: 51868

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.

Upvotes: 1

Related Questions