用一个merge语句表达以下四个要求student表中的数据同步到newstudent表中;在student中插入一行数据,再同步到newstudent;更改新插入同学的出生日期后再同步到newstudent;删除新学生记录后同步到newstudent的全部同步数据操作
时间: 2024-01-24 22:20:33 浏览: 23
MERGE INTO newstudent ns
USING student s
ON (ns.student_id = s.student_id)
WHEN MATCHED THEN
UPDATE SET ns.student_name = s.student_name, ns.student_age = s.student_age, ns.student_birthday = s.student_birthday
WHEN NOT MATCHED THEN
INSERT (ns.student_id, ns.student_name, ns.student_age, ns.student_birthday)
VALUES (s.student_id, s.student_name, s.student_age, s.student_birthday);
INSERT INTO student (student_id, student_name, student_age, student_birthday)
VALUES (5, 'John', 22, '2000-01-01');
MERGE INTO newstudent ns
USING student s
ON (ns.student_id = s.student_id)
WHEN MATCHED THEN
UPDATE SET ns.student_name = s.student_name, ns.student_age = s.student_age, ns.student_birthday = s.student_birthday
WHEN NOT MATCHED THEN
INSERT (ns.student_id, ns.student_name, ns.student_age, ns.student_birthday)
VALUES (s.student_id, s.student_name, s.student_age, s.student_birthday);
UPDATE student SET student_birthday = '2001-02-02' WHERE student_id = 5;
MERGE INTO newstudent ns
USING student s
ON (ns.student_id = s.student_id)
WHEN MATCHED THEN
UPDATE SET ns.student_name = s.student_name, ns.student_age = s.student_age, ns.student_birthday = s.student_birthday
WHEN NOT MATCHED THEN
INSERT (ns.student_id, ns.student_name, ns.student_age, ns.student_birthday)
VALUES (s.student_id, s.student_name, s.student_age, s.student_birthday);
DELETE FROM student WHERE student_id = 5;
MERGE INTO newstudent ns
USING student s
ON (ns.student_id = s.student_id)
WHEN MATCHED THEN
UPDATE SET ns.student_name = s.student_name, ns.student_age = s.student_age, ns.student_birthday = s.student_birthday
WHEN NOT MATCHED THEN
INSERT (ns.student_id, ns.student_name, ns.student_age, ns.student_birthday)
VALUES (s.student_id, s.student_name, s.student_age, s.student_birthday);
相关推荐
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)