How To Update a Column Based on Another Column in SQL

Sometimes you may need to update a column in table based on value of another column in table. Here is 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

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

There are two ways to update column based on value of another column – using WHERE clause and using CASE statement.

Update column based on another column using WHERE clause

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

Update column based on another column 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

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_table 
SET first_table.column1 = second_table.column2 
WHERE 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       |
+------+------------+-----------+

Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.