你这个表有多少条记录,如果上百万条的话,建议不要用delete。把你想留下来的数据select into 临时表中,把原表truncate table,然后再导回去。select 比delete可快多了
用left join 怎么样?可以试一下。数据量很大么?如果数据量很大的华 可以先把 select gyqccarduser.assetsCfg.serialcode into @tb from gyqccarduser.assetsCfg,data$ where gyqccarduser.assetsCfg.serialcode=data$.serialcode and gyqccarduser.assetsCfg.assetsname=data$.assetsname and gyqccarduser.assetsCfg.AssetsUseDate=data$.AssetsUseDate and gyqccarduser.assetsCfg.serialcode != '0700096S' 放到一个表中。然后在 delete from gyqccarduser.assetsCfg where serialcode in (select serialcode from @tb)这样试一下
delete gyqccarduser.assetsCfg from gyqccarduser.assetsCfg m where serialcode <> '0700096S' and exists (select 1 from [data$] n where serialcode = m.serialcode and assetsname = m.assetsname and AssetsUseDate = m.AssetsUseDate)
--方法一 delete gyqccarduser.assetsCfg from gyqccarduser.assetsCfg m where serialcode <> '0700096S' and exists (select 1 from [data$] n where serialcode = m.serialcode and assetsname = m.assetsname and AssetsUseDate = m.AssetsUseDate) --方法二 delete gyqccarduser.assetsCfg from gyqccarduser.assetsCfg m , [data$] n where m.serialcode <> '0700096S' and where n.serialcode = m.serialcode and n.assetsname = m.assetsname and n.AssetsUseDate = m.AssetsUseDate
delete gyqccarduser.assetsCfg from data$ where gyqccarduser.assetsCfg.serialcode=data$.serialcode and gyqccarduser.assetsCfg.assetsname=data$.assetsname and gyqccarduser.assetsCfg.AssetsUseDate=data$.AssetsUseDate and (gyqccarduser.assetsCfg.serialcode > '0700096S' or gyqccarduser.assetsCfg.serialcode < '0700096S')
select gyqccarduser.assetsCfg.serialcode into @tb from gyqccarduser.assetsCfg,data$
where gyqccarduser.assetsCfg.serialcode=data$.serialcode and gyqccarduser.assetsCfg.assetsname=data$.assetsname and
gyqccarduser.assetsCfg.AssetsUseDate=data$.AssetsUseDate
and gyqccarduser.assetsCfg.serialcode != '0700096S'
放到一个表中。然后在
delete from gyqccarduser.assetsCfg
where serialcode in (select serialcode from @tb)这样试一下
--方法一
delete gyqccarduser.assetsCfg from gyqccarduser.assetsCfg m where serialcode <> '0700096S' and exists (select 1 from [data$] n where serialcode = m.serialcode and assetsname = m.assetsname and AssetsUseDate = m.AssetsUseDate) --方法二
delete gyqccarduser.assetsCfg from gyqccarduser.assetsCfg m , [data$] n where m.serialcode <> '0700096S' and where n.serialcode = m.serialcode and n.assetsname = m.assetsname and n.AssetsUseDate = m.AssetsUseDate
from data$
where gyqccarduser.assetsCfg.serialcode=data$.serialcode
and gyqccarduser.assetsCfg.assetsname=data$.assetsname
and gyqccarduser.assetsCfg.AssetsUseDate=data$.AssetsUseDate
and (gyqccarduser.assetsCfg.serialcode > '0700096S' or gyqccarduser.assetsCfg.serialcode < '0700096S')