sql server 批量删除

sql server 批量删除数据

创建表1

1 create table  #table1 (
2 
3 id int primary key identity(1,1),
4 msg varchar(24),
5 loc varchar(24),
6 style varchar(24)
7 )

创建表2

 1 --表2
 2 create table #table2 (
 3 
 4 msg varchar(20),
 5 loc varchar(20),
 6 
 7 money1 money  
 8 
 9 
10 )

插入数据

 1 insert into #table1 (msg,loc,style) values (
 2 '张三','山西','教师'
 3 
 4 )
 5 insert into #table1 (msg,loc,style) values (
 6 '李四','山东','医生'
 7 
 8 )
 9 insert into #table1 (msg,loc,style) values (
10 '王五','山西','农民'
11 
12 )
13 insert into #table1 (msg,loc,style) values (
14 '赵六','山东','工人'
15 
16 )

 1 insert into #table2 (msg,loc,money1) values (
 2 
 3 
 4 '张三','山西',20
 5 
 6 
 7 )
 8 insert into #table2 (msg,loc,money1) values (
 9 
10 
11 '李四','山东',30
12 
13 
14 )
15 insert into #table2 (msg,loc,money1) values (
16 
17 
18 '王五','山西',10
19 
20 
21 )
22 insert into #table2 (msg,loc,money1) values (
23 
24 
25 '赵六','山东',15
26 
27 
28 )

执行后界面

现需求:

-- 现要求删除职业大于15 的人员

delete a from #table1 as a

inner join #table2 as b
with(nolock) on a.msg =b.msg and a.loc =b.loc
where isnull(b.money1,0)>15

红色:关联表

黄色:表

紫色:关联项

蓝色:条件

执行语句后界面