Software environment: 1, Windows NT4.0 Oracle 8.0.4 2, the ORACLE installation path is: c: ORANT problem proposes: When doing data dump business, if an operation error occurs, there may be a primary table and a silent table. There are the same data, so the result of this settlement may have an error.
Implementation: SQL> Create Table A (2 BM Char (4), - Code 3 MC VARCHAR2 (20) - Name 4) 5 / Table has been established. SQL> INSERT INTO A VALUES ('1111', '1111' ); SQL> INSERT INTO A VALUES ('1112', '1111'); SQL> INSERT INTO A VALUES ('1113', '1111'); SQL> INSERT INTO A VALUES ('1114', '1111'); SQL> INSERT INTO A VALUES ('1115', '1111'); SQL> Create Table B AS Select * from a where 1 = 2; Table has been established. SQL> INSERT INTO B VALUES ('1111', '1111') ; SQL> INSERT INTO B VALUES ('1112', '1111'); SQL> INSERT INTO B VALUES ('1113', '1111'); SQL> INSERT INTO B VALUES ('1114', '1111'); SQL > commit; completely submit. SQL> Select * from a; BM MC ---- -------------------- 1111 1111 1112 1111 1113 1111 1114 1111 1115 1111 SQL > Select * from b; BM MC ------------------------ 1111 1111 1112 1111 1113 1111 1114 1111 1111 1111 1114 1111 1111 1114 1111 1111 1114 1111 1111 1114 1111 1111 1114 1111 1111 1114 1111 1111 1114 1111 1111 1114 1111 1111 1114 1111 1111 1114 1111 1111 1114 1111 1111 1114 1111 1111 1114 1111 1111 1114 1111 1111 1114 1111 1111 1114 1111 1111 1114 1111 1111 1114 1111 1111 1114 1111 1111 1114 1111 1111 1114 1111 1111 1114 1111 1111 1114 1111
Method 1: EXISTS clause sql> delete from a where exists (select 'x' from b where A.bm = b.bm and a.mc = b.mc); Delete 4 records. Where Condition: If two tables In the primary key of the same field, you can use the two primary keys. 2: in clause sql> delete from a where (bm, mc) in (SELECT BM, MC from B); delete 4 records. SQL> SELECT * FROM A; BM MC ---- -------------------- 1115 1111 Real test conclusion: When the table is not very big, It can be tolerated with the in clause, and if the amount of records is large (more than 100,000), the in clause is hard to endure, and the speed is very slow.
