MySQL Join Update Techniques for Synchronizing Related Tables

In many real‑world applications, such as e‑commerce or school management systems, it is necessary to update a column in one table based on related data in another table. This guide walks through several MySQL techniques for performing such relational updates.

1. Preparation

Two demonstration tables are created: t_order (order information) and t_product (product catalog). The tables are linked by product_id . Sample data is then inserted into both tables.

CREATE TABLE `t_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `product_id` int(11) DEFAULT NULL COMMENT '商品id',
  `product_name` varchar(32) DEFAULT NULL COMMENT '商品名称',
  `amout` decimal(10,2) DEFAULT NULL COMMENT '订单金额',
  `order_time` timestamp NULL DEFAULT NULL COMMENT '下单时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `t_product` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `product_name` varchar(32) DEFAULT NULL COMMENT '商品名称',
  `create_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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');

After execution, the tables contain the data shown in the accompanying screenshots.

2. Updating via JOIN

An INNER JOIN can be used to copy the latest product name into the order table:

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

The result updates rows where the product exists; rows with no matching product remain unchanged. Using LEFT JOIN instead changes unmatched rows to NULL , as demonstrated with product_id = 128 .

3. Updating via Subquery

The same effect can be achieved with a correlated subquery:

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

This also sets the product name to NULL when no matching product is found.

4. Multi‑table UPDATE

MySQL allows listing multiple tables in the UPDATE clause:

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

This behaves like the INNER JOIN version, affecting only rows with matching products.

5. Conclusion

Relational updates are common in both development and database administration. Depending on the join type, unmatched rows may be left unchanged or set to NULL . Choose the method that fits the business requirement.

Finally, the author asks readers to like, share, and follow the article to encourage further content creation.