34,575
社区成员
发帖
与我相关
我的任务
分享SQL语句问题 高手进！！
有两个表
table1 table2 他们的imagesign 是相同的
现在知道 table1 的ID 号，
求一条SQL语句 根据table1的ID 同时 删掉table1 和 table2 的 与 imagesign 对应的列！！
...全文
请发表友善的回复…
发表回复
坚_持 2013-04-07
- 打赏
- 举报
--用触发器随便写了一下
--表一
if object_id('t1','u') is not null
drop table t1
go
create table t1
(
id int identity(1,1),
smallimage varchar(20),
imagedate datetime,
imagesign varchar(10)
)
insert into t1 select '12345678',getdate(),'1234567' union all
select '12345678',getdate(),'12345678'
--表二
if object_id('t2','u') is not null
drop table t2
go
create table t2
(
id int identity(1,1),
imagePath varchar(10),
imagesign varchar(10)
)
insert into t2 select '1324','1234567' union all select '1325','1234567' union all
select '1334','1234567' union all select '2324','12345678' union all select '3324','12345678'
select * from t1
select * from t2
--建立触发器
if object_id(N'tr_imagesign',N'tr') is not null
drop table tr_imagesign
go
create trigger tr_imagesign
on t1
for delete
as
begin
delete t2 from deleted as d where t2.imagesign = d.imagesign
end
--删除table1表的id=1的数据
delete t1 where id = 1
--查看删除后剩余的数据
select * from t1
select * from t2
--建立触发器
if object_id(N'tr_imagesign',N'tr') is not null
drop table tr_imagesign
go
create trigger tr_imagesign
on t1
for delete
as
begin
delete t2 from deleted as d where t2.imagesign = d.imagesign
end
--删除table1表的id=1的数据
delete t1 where id = 1
--查看删除后剩余的数据
select * from t1
select * from t2
弘恩 2013-04-07
- 打赏
- 举报
老黄说的很正确呀
發糞塗牆 2013-04-07
- 打赏
- 举报
其实你这个最好用级联删除，这样就不用写两次，级联可以看我的文章：
http://blog.csdn.net/dba_huangzj/article/details/8042999
另外完事请结贴
兔子侠客 2013-04-07
- 打赏
- 举报
DELETE table2 FROM table2 INNER JOIN TABLE1 ON table2.imagesign =table1.imagesign
WHERE TABLE1.id=需要删除的id
能删除 table2 的数据
table1 的得再写一次 DELETE FROM table1 WHERE TABLE1.id=需要删除的id
不过也行了 多谢
發糞塗牆 2013-04-07
- 打赏
- 举报
我的语句试了没？
兔子侠客 2013-04-07
- 打赏
- 举报
就是删除数据
發糞塗牆 2013-04-07
- 打赏
- 举报
删除列？如果是列的话只能update而不是delete發糞塗牆 2013-04-07
- 打赏
- 举报
DELETE table2 FROM table2 INNER JOIN TABLE1 ON table2.imagesign =table1.imagesign
WHERE TABLE1.id=需要删除的id
GO
DELETE FROM table1 WHERE TABLE1.id=需要删除的id
