Update a column in MySQL

Question

I have a table table1 with three columns and a bunch of rows:

[key_col|col_a|col_b]

I want to update col_a with a set of values (i.e. leaving col_b unchanged), something like this:

INSERT INTO table1 AS t1 (key_col, col_a) VALUES ("k1", "foo"), ("k2", "bar");

But it doesn't work, how do I do this?

**UPDATE** the answer at was what I was looking for http://stackoverflow.com/questions/3432/multiple-updates-in-mysql — Muleskinner, Feb 01 '12 at 10:14

score 128 · Answer 1 · edited Oct 03 '17 at 07:09

128

You have to use UPDATE instead of INSERT:

For Example:

UPDATE table1 SET col_a='k1', col_b='foo' WHERE key_col='1';
UPDATE table1 SET col_a='k2', col_b='bar' WHERE key_col='2';

edited Oct 03 '17 at 07:09

answered Jun 28 '11 at 08:43

Naveed

so you are saying that i need to write one update query for each row I want to update? – Muleskinner Jun 28 '11 at 08:51
Read this article for multiple rows update: http://www.karlrixon.co.uk/articles/sql/update-multiple-rows-with-different-values-and-a-single-sql-query/ – Naveed Jun 28 '11 at 10:06
8

The link `Update Multiple Rows With Different Values and a Single SQL Query` is broken. Fix or remove please. – nodebase May 18 '17 at 04:37
You can use a batch from your client application . – I.Tyger Nov 21 '17 at 23:12

score 12 · Answer 2 · answered Jun 28 '11 at 08:47

12

UPDATE table1 SET col_a = 'newvalue'

Add a WHERE condition if you want to only update some of the rows.

answered Jun 28 '11 at 08:47

Chris Snowden

could you give an example where multiply rows are updated in same query – Muleskinner Jun 28 '11 at 08:54
3

This would update all rows. Use a where to limit this to less rows. – Chris Snowden Jun 28 '11 at 08:57

score 6 · Answer 3 · edited Dec 17 '18 at 10:19

6

This is what I did for bulk update:

UPDATE tableName SET isDeleted = 1 where columnName in ('430903GW4j683537882','430903GW4j667075431','430903GW4j658444015')

edited Dec 17 '18 at 10:19

radbyx

answered Jan 23 '18 at 15:46

Abhay Shiro

score 3 · Answer 4 · edited Jun 02 '17 at 16:32

3

if you want to fill all the column:

update 'column' set 'info' where keyID!=0;

edited Jun 02 '17 at 16:32

Shawn Mehan

answered Jun 02 '17 at 16:04

kairos

score 1 · Answer 5 · answered Jun 28 '11 at 08:43

1

If you want to update data you should use UPDATE command instead of INSERT

answered Jun 28 '11 at 08:43

piotrpo

5 Answers5