mysql update join – MySQL update with JOIN and WHERE clause

mysql update join with syntax, MySQL UPDATE JOIN examples, INNER JOIN clause, LEFT JOIN and MySQL UPDATE JOIN with the INNER JOIN and LEFT JOIN clauses to perform the cross-table update.

mysql update join

MySQL update table based on another tables value – SQL UPDATE JOIN could be used to update one table using another table and join condition.

UPDATE dbTbl2
       JOIN dbTbl1
       ON dbTbl2.SERIAL_ID = dbTbl1.SUBST_ID
SET    dbTbl2.BRANCH_ID = dbTbl1.CREATED_ID;

MySQL UPDATE JOIN syntax

UPDATE Table1, Table2,
[INNER JOIN | LEFT JOIN] Table1 ON Table1.C1 = Table2. C1
SET Table1.C2 = Table2.C2, 
    Table2.C3 = expr
WHERE condition

Don’t miss : MySQL Multicolumn UPDATE JOIN Subquery

MySQL UPDATE JOIN examples

CREATE DATABASE IF NOT EXISTS memdb;

USE memdb;

-- create tables
CREATE TABLE marks (
    staging INT(11) NOT NULL,
    avarage FLOAT NOT NULL,
    PRIMARY KEY (staging)
);

CREATE TABLE members (
    mem_id INT(11) NOT NULL AUTO_INCREMENT,
    mem_name VARCHAR(255) NOT NULL,
    staging INT(11) DEFAULT NULL,
    income FLOAT DEFAULT NULL,
    PRIMARY KEY (mem_id),
    CONSTRAINT fk_staging FOREIGN KEY (staging)
        REFERENCES marks (staging)
);
-- insert data for marks table
INSERT INTO marks(staging,avarage)
VALUES(1,0),
      (2,0.01),
      (3,0.03),
      (4,0.05),
      (5,0.08);
-- insert data for members table
INSERT INTO members(mem_name,staging,income)      
VALUES('mayur dave', 1, 50000),
      ('chandni sojitra', 3, 65000),
      ('sushila ghadiya', 4, 75000),
      ('geeta donga', 5, 125000),
      ('nayna jethaliya', 3, 85000),
      ('jay dave', 2, 45000),
      ('lalo bhimni', 3, 55000);

MySQL UPDATE JOIN example with INNER JOIN clause

UPDATE members
        INNER JOIN
    marks ON members.staging = marks.staging 
SET 
    income = income + income * avarage;

MySQL UPDATE JOIN example with LEFT JOIN

INSERT INTO members(mem_name,staging,income)
VALUES('chetswar pujara',NULL,566569),
      ('bumrah new',NULL,120548);

I hope you get an idea about mysql update join.
I would like to have feedback on my infinityknow.com.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.