Last updated on August 8th, 2024 at 07:55 am
Every database allows you to update one or more columns in your tables. You can update them based on their own values, values of another column(s) in the same table. Sometimes you may need to update a column in table based on value of another column in table. In this article, we will learn how to update a column based on another column in SQL Server, MySQL, PostgreSQL.
How To Update a Column Based on Another Column in SQL
There are a couple of different ways to update database columns. Here are the steps to update a column based on another column in SQL.
Let us say you have the following table employees(id, first_name, last_name)
mysql> create table employees(id int,
first_name varchar(255),
last_name varchar(255));
mysql> insert into employees(id, first_name, last_name)
values(1,'John','Doe'),
(2,'Jane','Doe');
mysql> select * from employees;
+------+------------+-----------+
| id | first_name | last_name |
+------+------------+-----------+
| 1 | John | Doe |
| 2 | Jane | Doe |
+------+------------+-----------+
Also read : How to Update Multiple Columns in MySQL
Since we need to update one or more columns in one table based on another column’s values, it is called as conditional update, instead of updating all values of a column. There are two ways to update column based on value of another column – using WHERE clause and using CASE statement.
1. Using WHERE clause
In this approach, we use a WHERE clause in the UPDATE…SET statement. The database will look for rows matching the WHERE condition and then update column values of those rows only. Here is its syntax.
UPDATE table_name
SET column_name = new_value
WHERE column_name = old_value;
Here is the SQL query to change the first_name column value from John to Tim.
mysql> update employees
set first_name='Tim'
where first_name='Jim';
mysql> select * from employees;
+------+------------+-----------+
| id | first_name | last_name |
+------+------------+-----------+
| 1 | Tim | Doe |
| 2 | Jane | Doe |
+------+------------+-----------+
You can also use another column name in WHERE clause to update a column based on another column’s value.
UPDATE table_name
SET column_name1 = new_value
WHERE column_name2 = other_value;
Here’s the SQL query to update first_name column based on value of id columns using WHERE clause.
mysql> update employees
set first_name='Tim'
where id=1;
mysql> select * from employees;
+------+------------+-----------+
| id | first_name | last_name |
+------+------------+-----------+
| 1 | Tim | Doe |
| 2 | Jane | Doe |
+------+------------+-----------+
In the above statement, the UPDATE statement will first select rows that match the WHERE clause and update value of our column first_name
Also read : How to Escape Single quotes, special characters in MySQL
You can also use logical operators like AND/OR in your WHERE clause as shown below.
mysql> update employees
set first_name='Tim'
where id=1 or id=3;
You can also use an IN operator in WHERE clause as shown below.
mysql> update employees
set first_name='Tim'
where id in (1,3);
You can also use another SELECT query in your WHERE clause as shown below.
mysql> update employees
set first_name='Tim'
where id in ( select id from emp2 );
In this case, all those rows whose id value matches one of the values returned by SELECT query, will be updated.
Also read : How to Alter column from Null to Not Null
2. Using CASE statement
Here’s the SQL query to update first_name column based on value of id column using CASE statement.
mysql> update employees
set first_name = (CASE
WHEN id = 1
THEN 'Tim'
WHEN id = 2
THEN 'Dave'
END);
mysql> select * from employees;
+------+------------+-----------+
| id | first_name | last_name |
+------+------------+-----------+
| 1 | Tim | Doe |
| 2 | Dave | Doe |
+------+------------+-----------+
We use a CASE statement to specify new value of first_name column for each value of id column. This is a much better approach than using WHERE clause because with WHERE clause we can only change a column value to one new value. With CASE statement, we can update our column value to various values, depending on the individual values of id column.
Also Read : How to Fix Incorrect String Value in MySQL
3. Update Column Based on Another Table
You can also update column in one table from another column in a different table. Let us say you also have another table emp2(id, first_name, last_name) and you want to update first_name in employees table to first_name in emp2 table. Both tables also have same id column values.
mysql> create table emp2(id int,
first_name varchar(255),
last_name varchar(255));
mysql> insert into emp2(id, first_name, last_name)
values(1,'Don','Stone'),
(2,'Jim','Stew');
mysql> select * from emp2;
+------+------------+-----------+
| id | first_name | last_name |
+------+------------+-----------+
| 1 | Don | Stone |
| 2 | Jim | Stew |
+------+------------+-----------+
Also Read : Top MySQL Blogs for Database Administrators
In such a case, you can use the following UPDATE statement syntax to update column from one table, based on value of another table.
UPDATE first_table, second_tableSET first_table.column1 = second_table.column2WHERE first_table.id = second_table.table_id;
Here’s an SQL query to update first_name column in employees table to first_name column in emp2 table.
mysql> UPDATE employees, emp2 SET employees.first_name = emp2.first_name WHERE employees.id = emp2.id; mysql> select * from employees; +------+------------+-----------+ | id | first_name | last_name | +------+------------+-----------+ | 1 | Don | Doe | | 2 | Jim | Doe | +------+------------+-----------+
Alternatively, you can also use a subquery to supply the list of values in WHERE clause as shown below.
UPDATE table1
SET column1 = value
WHERE column2 IN (
SELECT column3
FROM table2
WHERE column4 = some_value
);
Here is an example to update . Let us say you have the following two tables employees and emp2.
mysql> select * from employees;
+------+------------+-----------+
| id | first_name | last_name |
+------+------------+-----------+
| 1 | John | Doe |
| 2 | Jane | Doe |
+------+------------+-----------+
mysql> select * from emp2;
+------+------------+-----------+
| id | first_name | last_name |
+------+------------+-----------+
| 1 | Tim | Doe |
| 2 | Jane | Doe |
+------+------------+-----------+
mysql> UPDATE employees
SET last_name = 'David'
WHERE last_name IN (
SELECT last_name
FROM emp2
WHERE id = 1
);
mysql> select * from employees;
+------+------------+-----------+
| id | first_name | last_name |
+------+------------+-----------+
| 1 | John | David |
| 2 | Jane | David |
+------+------------+-----------+
You can also set the new value to the result of a subquery as shown below. Of course, this is complicated and shown only for the sake of completeness.
UPDATE table1
SET column1 = (
SELECT column2
FROM table2
WHERE table2.id = table1.id
)
Here is an example to update first_name column in employees table based on the first_name column in emp2 table, for matching id column values.
mysql> UPDATE employees
SET employees.first_name = (
SELECT emp2.first_name
FROM emp2
WHERE employees.id = emp2.id
);
mysql> select * from employees
+------+------------+-----------+
| id | first_name | last_name |
+------+------------+-----------+
| 1 | Tim | Doe |
| 2 | Jane | Doe |
+------+------------+-----------+
Conclusion
In this article, we have learnt several simple ways to update column value based on same or another column’s value in the same or another table. You can use these SQL queries in all major databases such as MySQL, PostgreSQL, SQL Server, Oracle.
Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.