Mysql update column with value from another table

This article will update columns in a MySQL table with values from another table using different methods.

Table of Contents:-

• Update column with value from another table using JOIN
• Update column with value from another table using INNER QUERY
• Update column with value from another table using CURSOR

Let us get started by creating the sample data. We will be creating two tables, employee_details and employee_rating, followed by inserting a few rows to them.

# create table employee_details 
CREATE TABLE employee_details
(
  emp_id int  NOT NULL AUTO_INCREMENT,
  emp_firstName varchar(255) DEFAULT NULL,
  emp_lastName varchar(255) DEFAULT NULL,
  emp_designation varchar(255) DEFAULT NULL,
  primary key(emp_id)
);
# insert rows to employee_details
INSERT INTO employee_details (emp_id, emp_firstName, emp_lastName, emp_designation) 
 VALUES(1,"Henry","Smith","Assistant Manager"),
 (2,"Richa","Johnson","Executive"),
 (3,"Veronica","Brown","Executive"),
 (4,"George","Jones","Executive"),
 (5,"Harsh","Garcia","Assistant Manager"),
 (6,"William","Jones","Assistant Manager"),
 (7,"Rebecca","Miller","Senior Manager"),
 (8,"Paul","Davis","Assistant Manager"),
 (9,"Pualou","Miller","Executive");
# create table employee_rating 
CREATE TABLE employee_rating(
   id int AUTO_INCREMENT,
   emp_id int,
   emp_name varchar(255),
   emp_rating int,
   primary key(id)
);
#insert rows to employee_rating;
INSERT INTO employee_rating (emp_id,emp_rating) 
 VALUES(1,1),
 (2,3),
 (3,4),
 (4,1),
 (5,2),
 (6,2),
 (7,2),
 (8,2),
 (9,1);

Execute select * to view the data in employee_details, employee_rating table.

SELECT * FROM employee_details;

Output:-

SELECT * FROM employee_rating;

Output:-

As we can see in the image2, the emp_name column is empty. We will update the same with emp_firstName, emp_lastName columns of employee_details table using JOINS, INNER QUERIES, and CURSORS.

Update column with values from another table using JOIN

INNER JOINs help us retrieve records from more than one table having a condition satisfied by both and are logically related to each other.

Example: Update column emp_name in table employee_rating with values from columns emp_firstName, emp_lastName of employee_details table.

Observe the below query for the solution using JOINS.

UPDATE employee_rating AS r
        INNER JOIN
    employee_details d ON r.emp_id = d.emp_id 
SET 
    r.emp_name = concat(d.emp_firstName ," ", emp_lastName);

Action Output:-

Explanation:-

• The above update query gets the rows with common emp_id in both the employee_rating and employee_details table using the INNER JOIN.
• The query then updates the emp_name column in the employee_rating table by concatenating the emp_firstName and emp_lastName from the employee_details table.
• The query uses the concat() function of MySQL to join the emp_firstName and emp_lastName separated by a space.

To verify if the update was successful in the employee_rating table, let us execute:

 SELECT * FROM employee_rating;

Output:-

The output in image_4 shows that emp_name is updated with first and last names from the employee_details table.

Update column with values from another table using INNER QUERY

An inner query is a sub-query within another MySQL query logically related to each other.

Example: Update column emp_name in table employee_rating with values from columns emp_firstName, emp_lastName of employee_details table.

Observe the below query for the solution using the inner query.

UPDATE employee_rating 
SET 
    employee_rating.emp_name = (SELECT 
            CONCAT(employee_details.emp_firstName,
                        ' ',
                        employee_details.emp_lastName)
        FROM
            employee_details
        WHERE
            employee_details.emp_id = employee_rating.emp_id);

Action Output:-

Explanation:-

• In the above update query, the inner-query gets the concatenated values of emp_firstName and emp_lastName from the employee_details table. The inner query will get the rows with common emp_id in both tables.
• The outer query updates the emp_name column of the employee_rating table with values fetched from the inner query.

To verify if the update was successful in the employee_rating table, let us execute:

 SELECT * FROM employee_rating;

Output:-

Update column with values from another table using CURSORS

Cursors are used to loop through rows in MYSQL queries.

Example: Update column emp_name in table employee_rating with values from columns emp_firstName, emp_lastName of employee_details table.

Observe the below query for the solution using cursors. We will be creating the cursor named cursor_EmpName within a procedure proc_updateRating and then calling it for execution.

DROP PROCEDURE IF EXISTS proc_updateRating;
DELIMITER ;;
CREATE PROCEDURE proc_updateRating()
BEGIN
  DECLARE CURSOR_FName VARCHAR(255);
  DECLARE CURSOR_LName VARCHAR(255);
  DECLARE CURSOR_Id INT;

  DECLARE done INT DEFAULT FALSE;
  DECLARE cursor_EmpName CURSOR FOR SELECT emp_id, emp_firstName, emp_lastName FROM employee_details;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN cursor_EmpName;
  loop_through_rows: LOOP
    FETCH cursor_EmpName INTO CURSOR_Id,CURSOR_FName,CURSOR_LName;
    IF done THEN
      LEAVE loop_through_rows;
    END IF;
    UPDATE employee_rating SET emp_name = CONCAT(CURSOR_FName," ", CURSOR_LName) WHERE emp_id= CURSOR_Id;
  END LOOP;
  CLOSE cursor_EmpName;
END;
#call the procedure
CALL proc_updateRating();

Action Output:-

Explanation:-

• Here, we are creating a procedure named proc_updateRating. Within the procedure, a cursor is created, namely cursor_EmpName.
• The cursor fetches emp_id, emp_firstName and emp_lastName from employee_details table into variables CURSOR_Id, CURSOR_FName and CURSOR_LName respectively for each row in employee_details table.
• Then the update is made to each row for the column emp_name of the table employee_rating by concatenating CURSOR_FName and CURSOR_LName where emp_id = CURSOR_Id.
• Close the cursor.
• Finally, execute the same : CALL proc_updateRating().

To verify if the update was successful in the employee_rating table, let us execute:

 SELECT * FROM employee_rating;

Output:-

READ MORE:

• MySQL Update with Inner Join
• MySQL Insert with Join
• MySQL: Insert in a loop

We hope this article helped you with MySQL updates on a table where the values get pulled from another table. Good Luck !!!.