两个表
gzk_hz 这个表中有个字段bmdm
另一个表bmk中 有2个字段 一个bmdm 一个yn
我想当bmk中字段yn=0时,将该记录所对应的bmdm在gzk_hz这个表中的记录删除掉.
比如当bmk中
bmdm yn
3501 1
3502 1
3503 0
3504 0gzk_hz
bmdm
3501
3502
3503
3504
这时我应该删除掉gzk_hz的3503和3504两条记录
sql语句该怎么写?
gzk_hz 这个表中有个字段bmdm
另一个表bmk中 有2个字段 一个bmdm 一个yn
我想当bmk中字段yn=0时,将该记录所对应的bmdm在gzk_hz这个表中的记录删除掉.
比如当bmk中
bmdm yn
3501 1
3502 1
3503 0
3504 0gzk_hz
bmdm
3501
3502
3503
3504
这时我应该删除掉gzk_hz的3503和3504两条记录
sql语句该怎么写?
insert @bmk select 3501,1
union all select 3502,1
union all select 3503,0
union all select 3504,0declare @gzk_hz table(bmdm int)
insert @gzk_hz select 3501
union all select 3502
union all select 3503
union all select 3504delete from @gzk_hz
where bmdm in (select bmdm from @bmk where yn=0)select * from @gzk_hz/*
bmdm
-----------
3501
3502
*/
where a.bmdm=b.bmdm and b.yn=0
from gzk_hz a,bmk b
where a.bmdm=b.bmdm and b.yn=0
delete from gzk_hz where bmdm in (select bmdm from bmk where yn=0)
insert @bmk select 3501,1
union all select 3502,1
union all select 3503,0
union all select 3504,0declare @gzk_hz table(bmdm int)
insert @gzk_hz select 3501
union all select 3502
union all select 3503
union all select 3504delete from @gzk_hz
where bmdm in (select bmdm from @bmk where yn=0)select * from @gzk_hz/*
bmdm
-----------
3501
3502
*/
declare @bmk table(bmdm int,yn int)
insert @bmk select 3501,1
union all select 3502,1
union all select 3503,0
union all select 3504,0declare @gzk_hz table(bmdm int)
insert @gzk_hz select 3501
union all select 3502
union all select 3503
union all select 3504DELETE @gzk_hz FROM @gzk_hz TB1, @bmk TB2
WHERE TB1.bmdm = TB2.bmdm
'触发器'
Create table bmk(bmdm int, yn int)
insert into bmk select 3501 ,1
insert into bmk select 3501 ,2
insert into bmk select 3501 ,3
insert into bmk select 3502,4
insert into bmk select 3501,0Create table gzk_hz(gzk_hz int)
insert into gzk_hz select 3501
insert into gzk_hz select 3502
insert into gzk_hz select 3502
insert into gzk_hz select 3503Create trigger bmk_table
on bmk
for delete
as
begin
delete from gzk_hz where gzk_hz in (select bmdm from deleted )
End
delete from bmk where yn=0select * from bmk
select * from gzk_hz
'3501 1
3501 2
3501 3
3502 4
'
-----
'3502
3502
3503
'