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 stackoverflow.com/questions/3432/multiple-updates-in-mysql — Muleskinner
– Muleskinner, Commented Feb 1, 2012 at 10:14

We're All Mad Here · Accepted Answer · 2017-10-03 07:09:45Z

134

You have to use UPDATE instead of INSERT:

UPDATE Syntax

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 3, 2017 at 7:09

We're All Mad Here

1,5544 gold badges19 silver badges46 bronze badges

answered Jun 28, 2011 at 8:43

Naveed

42.3k31 gold badges101 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Muleskinner Over a year ago

so you are saying that i need to write one update query for each row I want to update?

Naveed Over a year ago

Read this article for multiple rows update: karlrixon.co.uk/articles/sql/…

nodebase Over a year ago

The link Update Multiple Rows With Different Values and a Single SQL Query is broken. Fix or remove please.

I.Tyger Over a year ago

You can use a batch from your client application .

Chris Snowden · Accepted Answer · 2011-06-28 08:47:51Z

12

UPDATE table1 SET col_a = 'newvalue'

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

answered Jun 28, 2011 at 8:47

Chris Snowden

4,9921 gold badge27 silver badges34 bronze badges

2 Comments

Muleskinner Over a year ago

could you give an example where multiply rows are updated in same query

Chris Snowden Over a year ago

This would update all rows. Use a where to limit this to less rows.

radbyx · Accepted Answer · 2018-12-17 10:19:17Z

This is what I did for bulk update:

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

Shawn Mehan · Accepted Answer · 2017-06-02 16:32:19Z

3

if you want to fill all the column:

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

edited Jun 2, 2017 at 16:32

Shawn Mehan

4,60810 gold badges33 silver badges51 bronze badges

answered Jun 2, 2017 at 16:04

kairos

391 bronze badge

Comments

piotrpo · Accepted Answer · 2011-06-28 08:43:58Z

1

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

answered Jun 28, 2011 at 8:43

piotrpo

12.7k8 gold badges44 silver badges59 bronze badges

Collectives™ on Stack Overflow

Update a column in MySQL

5 Answers 5

4 Comments

2 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

2 Comments

Comments

Comments

Comments

Linked

Related