一、mysql兩張關聯表批量更新一張表存在、另一張表不存在的數據

創建user和user_order表

CREATE TABLE `user` (`id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,`id_card` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '身份證號',`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '名稱',`mobile` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '手機號',PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用戶';CREATE TABLE `user_order` (`id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,`user_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用戶id',`id_card` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '身份證號',`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '名稱',`mobile` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '手機號',PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用戶訂單';

更新sql

需求說明：訂單表中用戶名稱、身份證號、手機號、為空，用戶表中對應的三個字段都有數據，此種情況下，需要批量更新用戶訂單表中為空(用戶表的字段不為空)的數據，用一下sql即可解決

-- 更新
UPDATE user_order o
JOIN user u ON u.id = o.user_id
SET o.id_card = u.id_card,o.name = u.name,o.mobile = u.mobile
WHEREo.id_card IS NULL AND u.id_card IS NOT NULL	AND o.name IS NULL AND u.name IS NOT NULL	AND o.mobile IS NULL AND u.mobile IS NOT NULL

二、mysql 查詢數據后，插入到指定表中

事例表結構

CREATE TABLE `teacher_course` (`id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,`teacher_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '教師名稱',`course_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '課程名稱',`create_time` datetime DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='教師課程表';

查詢后插入

INSERT INTO teacher_course (teacher_name,course_name)
SELECT '張三' as teacher_name,course_name
FROM teacher_course where teacher_name = '張三';