MySQL – Join and Update Values From Another Table

In this post, we’re going to learn how to use MySQL Update Join query to update a mysql table column with the values from another table column using inner join.

mysql-inner-join-update

Query Syntax


UPDATE [table1_name] AS t1 
INNER JOIN [table2_name] AS t2 
ON t1.[column1_name] = t2.[column1_name] 
SET t1.[column2_name] = t2.[column2_name];

Properties

Replace all “[ ]” with the values of your table properties.

  • [table1_name] – This is the name of the table which you’re going to update which is represented as t1
  • [table2_name] – This is the table, from which your’re going to take the values which is represented as t2
  • t1.[column1_name] – This is the column which is going to link two tables (t2.[column1_name]) using inner join
  • t1.[column2_name] – This is the column which is going to be updated with the values from t2.[column2_name]

Example


UPDATE student_marks AS t1 
INNER JOIN student_profile AS t2 
ON t1.student_id = t2.student_id 
SET t1.student_name = t2.student_name;

If you want to add a where clause with this query.


UPDATE student_marks AS t1 
INNER JOIN student_profile AS t2 
ON t1.student_id = t2.student_id 
SET t1.student_name = t2.student_name 
WHERE t1.student_id <= 100;

I hope this helps you to use MySQL update join query. Share this post and comment your doubts below if you have any.


Anand Roshan

Author: Anand Roshan

An entrepreneur, programmer and a passionate artist who loves to work independently. Get social: Twitter | Google + | Artist Page

7 comments on “MySQL – Join and Update Values From Another Table”

  1. I’m using this syntax in MySQL Workbench 5.6 and getting the error code 1175: ‘You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column’
    My inner join column is using the key column, so I don’t understand what is wrong.

  2. Error Number: 1052

    Column ‘username’ in field list is ambiguous

    UPDATE user AS u JOIN admin AS a ON u.user_id = a.admin_id SET username= ‘hardik’ where user_id = ‘166’

    Filename: C:wampwwwhardikwebservice_dbsystemdatabaseDB_driver.php

    Line Number: 330

  3. Model:-

    function user_delete_join($user_id)
    {

    $this->db->query(“DELETE u a FROM user AS u INNER JOIN admin AS a ON u.user_id = a.admin_id where user_id = ‘$user_id'”);

    return $this->db->query(array($user_id));
    }

    Controller:-

    function user_update_join_post()

    {

    $username = $this->post(‘username’);

    $user_id = $this->post(‘user_id’);

    $result = $this->mdl_api_user->update_user_join($username,$user_id);

    if($result == FALSE)

    {

    $this->response(array(‘status’ => ‘failed’));

    }

    else

    {

    $this->response(array(‘status’ => ‘successfully updated’));

    }

    }

Leave a Reply

Your email address will not be published. Required fields are marked *