Sql1:
Delete from table1 t1 left join table2 t2 on t1. The field1=t2. The field1 and t1. Field2=t2. Field2 and t1. Field3=t2. Field3
Where t2. Field3 is not null

Sql2:
The delete from table1 t1
Where the exists (select 1 from table2 t2 where t1. The field1=t2. The field1 and t1. Field2=t2. Field2 and t1, field3=t2. Field3
Where t2. Field3 is not null)

Which can guarantee field3 in table1 is there must be, that the two SQL delete data are the same? O guide
In the execution of large amount of data (must) deleted sql2 will remove super slow, can replace sql1

CodePudding user response:

Can be replaced, perform the result is the same,

CodePudding user response:

Generally remove speed: drop> Truncate & gt; Delete, if you want to delete some data with the delete, pay attention to take the where clause, rollback segments are big enough, want to delete table, of course, with the drop
Wants to keep the table and will delete all data, if has nothing to do with the transaction, using truncate, if and relevant affairs, or you want to trigger the trigger, or do you want to see you, use the delete but be careful or use the drop and truncate, especially there is no backup. Or cry all can't find the Pacific ah!!!!!!

CodePudding user response:

Oracle does not support the join connection do the update and delete

CodePudding user response:

The delete from table1 t1
Where the exists (select 1 from table2 t2 where t1. The field1=t2. The field1 and t1. Field2=t2. Field2 and t1, field3=t2. Field3
Where t2. Field3 is not null) you see field3 conditions permit is empty? Also as an associated fields?
If it is
Where the length (t2) field3) & gt; 0 try

CodePudding user response:

Do not delete the huge amounts of data directly, it is recommended that: backup guide table1_bak table1 data first, then truncate the table1, and then insert table1_bak don't delete in table1, before the operation, must be primary keys, indexes to delete, data processing after the completion of the primary key again, the indexes,

CodePudding user response:

Delete it

CodePudding user response:

A large quantity of data table and do the partition table, delete the time will be convenient,

CodePudding user response:

On both sides of the results is not the same, if field3 must exist, so the first statement, will all the data in table1 should be delete, delete and table2 related data of the second,
Sql2 is slow, can add index,