delete 表一 from 表二 where 表一.cid=表二id and datediff(dd,uptime,getdate())>=15试一下看行不行,没测试数据
delete table1 from table1 a,table2 b where a.cid = b.id and datediff(day,b.uptime,getdate()) > 15
--> 测试数据:[tbl1] if object_id('[tbl1]') is not null drop table [tbl1] create table [tbl1]([id] int,[title] varchar(17)) insert [tbl1] select 1,'2012星光大道:张三' union all select 2,'2012星光大道:李四' union all select 3,'2013星光大道:王五'go if object_id('[tbl2]') is not null drop table [tbl2] create table [tbl2]( [cid] int, [date] date ) go insert tbl2 select 1,'2012-03-12' union all select 3,'2012-02-01'delete tbl1 from tbl2 where tbl1.id=tbl2.cid and DATEDIFF(DD,tbl2.[date],GETDATE())>=15 select * from tbl1 --cid=3数据对用的日期距离当前日期超过15天,删除成功 /* id title 1 2012星光大道:张三 2 2012星光大道:李四 */
delete t2 from t2 , t1 where t2.id = t1.cid and datediff(dd,uptime,getdate()) > 15
delete a from table1 as a where exists( select 1 from Table2 where uptime<cnvert(varchar(10),getdate()-15,120) and ID=a.Cid )delete Table2 where uptime<cnvert(varchar(10),getdate()-15,120)
--> 测试数据:[tbl1]
if object_id('[tbl1]') is not null drop table [tbl1]
create table [tbl1]([id] int,[title] varchar(17))
insert [tbl1]
select 1,'2012星光大道:张三' union all
select 2,'2012星光大道:李四' union all
select 3,'2013星光大道:王五'go
if object_id('[tbl2]') is not null drop table [tbl2]
create table [tbl2](
[cid] int,
[date] date
)
go
insert tbl2
select 1,'2012-03-12' union all
select 3,'2012-02-01'delete tbl1 from tbl2 where tbl1.id=tbl2.cid and DATEDIFF(DD,tbl2.[date],GETDATE())>=15
select * from tbl1
--cid=3数据对用的日期距离当前日期超过15天,删除成功
/*
id title
1 2012星光大道:张三
2 2012星光大道:李四
*/
delete t2 from t2 , t1 where t2.id = t1.cid and datediff(dd,uptime,getdate()) > 15
from table1 as a
where
exists(
select 1 from Table2 where uptime<cnvert(varchar(10),getdate()-15,120) and ID=a.Cid
)delete Table2 where uptime<cnvert(varchar(10),getdate()-15,120)