关于级联条件删除表数据
1. 表结构及数据
测试表:
Test1
字段名称
|
字段类型及精度
|
备注
|
A
|
Varchar(20)
|
|
B
|
Varchar(20)
|
|
数据
A
|
B
|
a
|
b
|
A1
|
B1
|
A2
|
B2
|
A3
|
B3
|
Test2
字段名称
|
字段类型及精度
|
备注
|
A
|
Varchar(20)
|
|
B
|
Varchar(20)
|
|
数据
A
|
B
|
a
|
b
|
A1
|
B1
|
2. 删除方式
我们一般删除表数据有如下几种方式:
1) Delete Test1
2) Delete from Test1
3) Delete from Test1 where A=’a’
4) Delete Test1 from Test1,Test2
5) Delete Test2 from Test1,Test2
6) Delete Test1 from Test1,Test2 where Test1.A=Test2.A
7) Delete Test2 from Test1,Test2 where Test1.A=Test2.A
8) Delete Test1 from Test1 where Test1.A in (select Test2.A from Test2)
9) Delete Test2 from Test1 inner join Test2 on (Test1.A=Test2.A)
10) Delete Test1 from Test1 where Test1.A in (select Test2.A from Test2) where Test1.A=’a’
11) Delete Test2 from Test1 where Test1.A in (select Test2.A from Test2) where Test2.A=’a’
3. 注意:
1) Delete时,如果from后面跟多个表,如from Test1,Test2 这是要这样写
delete Test1 from Test1,Test2 或者 delete Test2 from Test1,Test2
因为不这样写的话,delete 就不能明确的知道你到底要删除那一个表的数据
delete Test1 from Test1,Test2 或者 delete Test2 from Test1,Test2
因为不这样写的话,delete 就不能明确的知道你到底要删除那一个表的数据
2) 如果delete 后面跟的条件时级联的条件时,也要像上面那样明确指定要删除的表
Delete Test1 from Test1 where Test1.A in (select Test2.A from Test2)
Delete Test2 from Test1 inner join Test2 on (Test1.A=Test2.A)
Delete Test1 from Test1 where Test1.A in (select Test2.A from Test2)
Delete Test2 from Test1 inner join Test2 on (Test1.A=Test2.A)