Merge语句
2021/12/21 6:20:57
本文主要是介绍Merge语句,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
Merge语句
概念&用处
用来合并update和insert语句
通过merge语句,根据一张表(原数据表,source table)对另外一张(目标表,target table)表进行查询,连接条件匹配上的进行update,无法匹配的执行INSERT。
登录
sqlplus / as sysdba sqlplus scott/tiger
构建平台sql语句
create table KC ( kch VARCHAR2(3) not null, kcm VARCHAR2(16) not null, kkxq NUMBER(1) not null, xs NUMBER(2) not null, xf NUMBER(1) ) create table XS ( xh VARCHAR2(6), xm VARCHAR2(6), zym VARCHAR2(6), xb VARCHAR2(2), cssj DATE, zxf NUMBER(2), bz VARCHAR2(20) ) create table XS_KC ( xh CHAR(6) not null, kch CHAR(6) not null, cj INTEGER, xf NUMBER(2) ) insert into KC (kch, kcm, kkxq, xs, xf) values ('001', '001', 1, 11, 1); insert into KC (kch, kcm, kkxq, xs, xf) values ('001', '001', 1, 11, 1); insert into XS (xh, xm, zym, xb, cssj, zxf, bz) values ('061101', '王林', '计算机', '男', to_date('10-02-1986', 'dd-mm-yyyy'), 50, null); insert into XS (xh, xm, zym, xb, cssj, zxf, bz) values ('101112', '李明', '计算机', '男', to_date('30-01-1986', 'dd-mm-yyyy'), 36, null); insert into XS (xh, xm, zym, xb, cssj, zxf, bz) values ('001', '张琼', '计算机', null, null, 45, '三好学生'); insert into XS (xh, xm, zym, xb, cssj, zxf, bz) values ('121112', '王小二', '计算机', '男', to_date('30-01-1986', 'dd-mm-yyyy'), 36, null); insert into XS_KC (xh, kch, cj, xf) values ('061101', '101 ', 80, null); insert into XS_KC (xh, kch, cj, xf) values ('061101', '102 ', 78, null); insert into XS_KC (xh, kch, cj, xf) values ('061101', '206 ', 76, null); insert into XS_KC (xh, kch, cj, xf) values ('061103', '101 ', 82, null); insert into XS_KC (xh, kch, cj, xf) values ('061103', '102 ', 82, null); insert into XS_KC (xh, kch, cj, xf) values ('061103', '206 ', 83, null); insert into XS_KC (xh, kch, cj, xf) values ('061104', '101 ', 90, null); insert into XS_KC (xh, kch, cj, xf) values ('061107', '101 ', 98, null); insert into XS_KC (xh, kch, cj, xf) values ('061107', '102 ', 80, null); commit;
使用MERGE语句XS1表中新增的数据插入表XS中:
create table xs1 as select * from xs; insert into xs1(xh,xm,zym,xb,cssj,zxf) values('007','test','计算机','男',to_date('19900130','YYYYMMDD'),36) --使用MERGE语句XS_JSJ表中新增的数据插入表XS中: merge into xs a using xs1 b on(a.xh=b.xh) when not matched then insert(a.xh,a.xm,a.zym,a.xb,a.cssj,a.zxf) values(b.xh,b.xm,b.zym,b.xb,b.cssj,b.zxf);
构建平台
create table PRODUCTS ( PRODUCT_ID INTEGER, PRODUCT_NAME VARCHAR2(60), CATEGORY VARCHAR2(60) ); create table NEWPRODUCTS ( PRODUCT_ID INTEGER, PRODUCT_NAME VARCHAR2(60), CATEGORY VARCHAR2(60) ); insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS'); insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS'); insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS'); insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS'); insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD'); commit; insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS'); insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS'); insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS'); insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS'); commit;
products为目标表,newproducts为源表,则若产品号相匹配,根据源表信息修改目标表的产品名(product_name)和产品类别(category)
merge into products p using newproducts np on (p.product_id = np.product_id) when matched then update set p.product_name=np.product_name, p.category=np.category;
若产品号不匹配,则根据源表将新的纪录添加到目标表
merge into products p using newproducts np on (p.product_id=np.product_id) when not matched then insert values(np.product_id,np.product_name,np.category);
增删改
merge into products p using newproducts np on (p.product_id=np.product_id) when matched then update set p.product_name= np.product_name, p.category = np.category delete where(p.category='ELECTRNCS') when not matched then insert values(np.product_id,np.product_name,np.category);
这篇关于Merge语句的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-07-04TiDB 资源管控的对撞测试以及最佳实践架构
- 2024-07-03万字长文聊聊Web3的组成架构
- 2024-07-02springboot项目无法注册到nacos-icode9专业技术文章分享
- 2024-06-26结对编程到底难不难?答案在这里
- 2024-06-19《2023版Java工程师》课程升级公告
- 2024-06-15matplotlib作图不显示3D图,怎么办?
- 2024-06-1503-Loki 日志监控
- 2024-06-1504-让LLM理解知识 -Prompt
- 2024-06-05做软件测试需要懂代码吗?
- 2024-06-0514-ShardingSphere的分布式主键实现