⼀、业务场景
（1）主从两个表，主表Student，有字段id、name、sex，从表Boy，有字段id、name，主从表同⼀对象id相同 （2）从表Boy的name属性被业务修改，定时批量处理主表，以维持主表name属性与从表⼀致 ⼆、表结构
1.主表STUDENT
ID NAME sex
1zhangsan boy
2lisi girl
3wangwu boy
2.从表BOY
ID NAME
1张三
3王五
三、建表SQL（DDL）
1、主表 Student
-- DDL
CREATE TABLE student (
id NUMBER NOT NULL,
name VARCHAR2(255 BYTE)NULL,
sex VARCHAR2(255 BYTE)NULL
)
ALTER TABLE student ADD CHECK(id IS NOT NULL);
-- DML
INSERT INTO student VALUES('1','zhangsan','boy');
INSERT INTO student VALUES('2','lisi','girl');
南昌七城会
INSERT INTO student VALUES('3','wangwu','boy');
2、从表BOY
-- DDL
CREATE TABLE boy (
id NUMBER NOT NULL,
三民主义青年团name VARCHAR2(255 BYTE)NULL
)
-
思远双N
- DML
INSERT INTO boy VALUES('1','张三');
INSERT INTO boy VALUES('3','王五');
四、DML
1、基本语法
-- DML
UPDATE student s SET s.name ='张三'WHERE id =1;
2、变相
-- DML，0.015s
UPDATE student s
SET s.name =(
SELECT b.name FROM boy b WHERE s.id = b.id AND s.name != b.name )
WHERE EXISTS(
SELECT1FROM boy b WHERE s.id = b.id AND s.name != b.name
);
3、快速游标法
-- DML，0.014s
BEGIN
FOR cur IN(
SELECT s.id sid, b.name bname
FROM student s, boy b
WHERE s.id = b.id AND s.name != b.name AND s.sex ='boy'
)
loop
UPDATE student s SET s.name = cur.bname WHERE s.id = cur.sid;
END loop;
END;
-- DML，0.019s
UPDATE(
SELECT
s.name sname, b.name bname
FROM
student s, boy b
az91d镁合金WHERE
s.id = b.id AND s.name != b.name
)
产量定额SET sname = bname;
报错提⽰：ORA-01779: ⽆法修改与⾮键值保存表对应的列
参考资料，从表id必须增加主键约束，且为视图内的where条件： -- DDL
ALTER TABLE boy ADD CONSTRAINT pk_id PRIMARY KEY(id);
5、合并法（Merge）
MERGE INTO student s USING boy b ON(
s.id = b.id AND s.sex ='boy'AND s.name != b.name
)
WHEN MATCHED THEN
UPDATE SET s.name = b.name;
四川省西昌市报错提⽰：ORA-38104: ⽆法更新 ON ⼦句中引⽤的列
参考资料，错误原因是条件重复，正确写法：
-- DML，0.016s
MERGE INTO student s USING boy b ON(
s.id = b.id AND s.sex ='boy'
-- AND s.name != b.name
)
WHEN MATCHED THEN
UPDATE SET s.name = b.name;
豫公网安备41160202000603
站长QQ:729038198
关于我们
投诉建议