MySQL Multi-Table Join Updates: Four Methods Compared with Examples

Preparation: Schema and Test Data

Two tables are created: t_order (id, product_id, product_name, amout, order_time) and t_product (id, product_name, create_time), both using InnoDB and utf8mb4. Sample data is inserted:

INSERT INTO `t_order` VALUES ('1', '1', '科幻图书', '25.00', '2023-08-21 17:16:54');
INSERT INTO `t_order` VALUES ('2', '2', '台灯', '12.00', '2023-08-21 17:17:22');
INSERT INTO `t_order` VALUES ('3', '128', '篮球', '82.00', '2023-08-21 17:18:18');

INSERT INTO `t_product` VALUES ('1', '编程书籍', '2023-08-21 17:15:24');
INSERT INTO `t_product` VALUES ('2', '电饭锅', '2023-08-21 17:15:27');
INSERT INTO `t_product` VALUES ('3', '加薪神器', '2023-08-21 17:16:00');

Initial state: order rows 1 and 2 have product_id 1 and 2 matching product table; row 3 has product_id 128 which does not exist in product table.

Method 1: INNER JOIN Update

UPDATE t_order o
INNER JOIN t_product p ON o.product_id = p.id
SET o.product_name = p.product_name;

Result: Only rows with matching product_id (1 and 2) are updated. product_name becomes '编程书籍' and '电饭锅' respectively. Row with product_id 128 remains unchanged. Execution info shows 2 rows affected.

Method 2: LEFT JOIN Update

UPDATE t_order o
LEFT JOIN t_product p ON o.product_id = p.id
SET o.product_name = p.product_name;

Result: All three order rows are processed. For product_id 128 (no match in product table), product_name is set to NULL. Execution info shows 3 rows affected. This demonstrates the key difference: LEFT JOIN updates even non-matching rows, assigning NULL from the right table.

Method 3: Subquery Update

UPDATE t_order t
SET t.product_name = (
    SELECT product_name FROM t_product p WHERE t.product_id = p.id
);

Result: Identical to LEFT JOIN—3 rows affected, product_id 128 becomes NULL. The subquery returns NULL when no matching product row exists, so the SET clause assigns NULL.

Method 4: Direct Multi-Table UPDATE

UPDATE t_order o, t_product p
SET o.product_name = p.product_name
WHERE o.product_id = p.id;

Result: Same as INNER JOIN—only 2 rows affected, product_id 128 unchanged. The comma-separated table list with WHERE clause acts as an inner join.

Conclusion

Multi-table updates are common in both application code and database operations. The four methods differ in handling of non-matching rows: INNER JOIN and direct multi-table UPDATE update only matching rows (2 rows affected), while LEFT JOIN and subquery update all rows in the target table, setting columns to NULL when no match exists (3 rows affected). Choose the method based on whether you want to update non-matching rows.