How to update one column in MySQL?

In this tutorial, we’ll go over the various ways to update rows in a table using SQL progressing from more general updates to more specific methods.

Full Update

If every field needs to be updated to the same value, you can do that using a simple UPDATE command.

UPDATE table
SET col = new_value;

Conditional Update

To do a conditional update depending on whether the current value of a column matches the condition, you can add a WHERE clause which specifies this. The database will first find rows which match the WHERE clause and then only perform updates on those rows.

UPDATE table
SET col = new_value
WHERE col = old_value;

To expand on this, you can add anything to the WHERE clause you like as long as it’s a valid expression. So to perform an update based on the value of another column in the same table, you could execute the following:

UPDATE table
SET col = new_value
WHERE other_col = some_other_value;

Since the WHERE clause can contain any valid expression, you also have the possibility to do updates where multiple columns meet the criteria

UPDATE table
SET col = new_value
WHERE col = old_value
AND other_col = some_other_value;

UPDATE table
SET col = new_value
WHERE col = old_value
OR other_col = some_other_value;

As you can see, you can expand the WHERE clause as much as you’d like in order to filter down the rows for updating to what you need.

Now what happens if you want to update rows in one table based on the condition of another table? This question leads to a few different ways you could do this.

Since the WHERE clause can contain any valid expression, you could use a subquery:

UPDATE table
SET col = new_value
WHERE other_col IN (
SELECT other_col
FROM other_table
WHERE conditional_col = 1
);

You can also use a subquery in the

UPDATE table
SET col = new_value
WHERE col = old_value;

UPDATE table
SET col = (
SELECT other_col
FROM other_table
WHERE other_table.table_id = table.id
);

Perhaps an easier way is to specify multiple tables after the UPDATE clause. Only the

UPDATE table
SET col = new_value
WHERE col = old_value;

UPDATE table, other_table
SET table.col = other_table.other_col
WHERE table.id = other_table.table_id;

Similarly to expanding the WHERE clause, the amount of tables can be expanded to include all the tables you need if you have multiple tables that need to be joined on.

Let us first create a table −

mysql> create table DemoTable770 (
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   Value int
);
Query OK, 0 rows affected (0.65 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable770(Value) values(10);
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable770(Value) values(90);
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable770(Value) values(160);
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable770(Value) values(450);
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable770(Value) values(560);
Query OK, 1 row affected (0.21 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable770;

This will produce the following output -

+----+-------+
| Id | Value |
+----+-------+
|  1 |    10 |
|  2 |    90 |
|  3 |   160 |
|  4 |   450 |
|  5 |   560 |
+----+-------+
5 rows in set (0.00 sec)

Following is the query to update only a single column value −

mysql> update DemoTable770
   set Value=Value+150
   where Id=4;
Query OK, 1 row affected (0.52 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Let us check the description of the view −

mysql> select *from DemoTable770;

This will produce the following output -

+----+-------+
| Id | Value |
+----+-------+
|  1 |    10 |
|  2 |    90 |
|  3 |   160 |
|  4 |   600 |
|  5 |   560 |
+----+-------+
5 rows in set (0.00 sec)

How to update a single column in MySQL?

How do you update a specific column?

How to update one column for all rows in MySQL?

How do you update an existing column in SQL?