mysql语句编写（只更新product 表的字段mail_address为空的列）

Question

有上面两张表，编写sql
1，要将表product 表的字段mail_address为空的列的mail_address值更新为表addressbook表中字段为的值，关联字段为表 addressbook的字段tel_no和表product 的字段tel_no 为一对多的关系。 （只更新product 表的字段mail_address为空的列）

附建表sql：

---

-- Table structure for addressbook

---

DROP TABLE IF EXISTS addressbook;
CREATE TABLE addressbook (
regist_no int(11) NOT NULL COMMENT '注册编号',
name varchar(128) NOT NULL COMMENT '姓名',
address varchar(256) NOT NULL COMMENT '住址',
tel_no char(10) DEFAULT NULL COMMENT '电话号码',
mail_address char(20) DEFAULT NULL COMMENT '邮箱地址',
postal_code char(8) NOT NULL,
PRIMARY KEY (regist_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

-- Records of addressbook

---

INSERT INTO addressbook VALUES ('1', 'sewewe', 'werwrewr', '2', '陕西', '123');
INSERT INTO addressbook VALUES ('2', 'etret', '334353', '3', '北京', '546456');
INSERT INTO addressbook VALUES ('3', '564654', '456456', '5', '南京', '43534');

DROP TABLE IF EXISTS product;
CREATE TABLE product (
product_id char(4) NOT NULL,
product_name varchar(100) DEFAULT NULL,
product_type varchar(32) DEFAULT NULL,
sale_price int(11) DEFAULT NULL,
purchase_price int(11) DEFAULT NULL,
regist_date date DEFAULT NULL,
tel_no char(10) DEFAULT NULL,
mail_address char(20) DEFAULT NULL,
PRIMARY KEY (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

-- Records of product

---

INSERT INTO product VALUES ('1', '你爸爸', '1', '11', '1', '2021-07-14', '2', '陕西');
INSERT INTO product VALUES ('345', '你奶奶', '', null, null, null, '3', null);
INSERT INTO product VALUES ('3456', 'fgdf', '1', '2', '3', '2021-09-24', '2', null);
INSERT INTO product VALUES ('3459', 'ninhh', '', null, null, null, '5', null);
INSERT INTO product VALUES ('4', '4345', '3', '4', '4', '2021-09-24', '5', null);
INSERT INTO product VALUES ('6', '45', '5', '4', '3', '2021-09-24', '5', null);

Answer 1

update product set mail_address=(select b.mail_address  from addressbook b where b.tel_no=tel_no limit 1) where mail_address is null