有A,B,C 三个表,完成如下功能:
1, 查询 A 表有的数据,而B表没有的数据。(A表和B表的差异数据)
2, 把上面查询出的结果插入C 表,并且更新 A表的字段 A_1(更新方式:A_1 + 1 =A_1)。
SQL> select * from a;
ID A_1
---------- ----------
1 10
2 30
3 30
SQL> select * from b;
ID
----------
1
SQL> select * from c;
no rows selected
SQL> insert into c (id) select a.id from a,b where a.id=b.id(+) and b.id is null;
2 rows created.
SQL> commit;
Commit complete.
SQL> select * from c;
ID
----------
2
3
如果数据量不大,可以这样更新
SQL> update a set a_1=a_1+1 where rowid in(select a.rowid from a,b where a.id=b.id(+) and b.id is null);
2 rows updated.
SQL> commit;
Commit complete.
SQL> select * from a;
ID A_1
---------- ----------
1 10
2 31
3 31
1, 查询 A 表有的数据,而B表没有的数据。(A表和B表的差异数据)
2, 把上面查询出的结果插入C 表,并且更新 A表的字段 A_1(更新方式:A_1 + 1 =A_1)。
SQL> select * from a;
ID A_1
---------- ----------
1 10
2 30
3 30
SQL> select * from b;
ID
----------
1
SQL> select * from c;
no rows selected
SQL> insert into c (id) select a.id from a,b where a.id=b.id(+) and b.id is null;
2 rows created.
SQL> commit;
Commit complete.
SQL> select * from c;
ID
----------
2
3
如果数据量不大,可以这样更新
SQL> update a set a_1=a_1+1 where rowid in(select a.rowid from a,b where a.id=b.id(+) and b.id is null);
2 rows updated.
SQL> commit;
Commit complete.
SQL> select * from a;
ID A_1
---------- ----------
1 10
2 31
3 31