InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 17 Jun 2013 23:40:31 GMT20http://www.sqlservercentral.com/Forums/Topic1384415-391-1.aspx[quote][b]sqlquery-101401 (11/25/2012)[/b][hr]delete from #ExistingBackups where (Name not like '%master%' and Name not like '%model%' and Name not like '%msdb%') or (ISNUMERIC(Right(SUBSTRING(name,1,charindex('.',name)-1),8))=0) or (Right(SUBSTRING(name,1,charindex('.',name)-1),8) > GETDATE()-1) or (Name like '%.trn')or (Name like '%_diff%')[/quote] have you tried select for above Tsql and another thing , add one by one the where clause filers and then see where it is moving out if the sceneMon, 26 Nov 2012 01:01:44 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1384415-391-1.aspxThanks ,but it did not work out as separate delete-- separate delete works fine--Remove all the diff backups from delete cmd Delete from #ExistingBackups where Name like '%_diff%'--Remove all the Tran backups from delete cmd Delete from #ExistingBackups where Name like '%.trn'--Remove all the files that dont have Date stamp from delete cmd Delete from #ExistingBackups where ISNUMERIC(Right(SUBSTRING(name,1,charindex('.',name)-1),8))=0--Remove sys databases from delete cmd Delete from #ExistingBackups where Name like '%master%' or Name like '%model%' or Name like '%msdb%'--Remove all the files that are greater than threshold from delete cmd Delete from #ExistingBackups where Right(SUBSTRING(name,1,charindex('.',name)-1),8) > @LastBackupToKeep--************************************************************************--Does not work as above delete from #ExistingBackups where (Name not like '%master%' and Name not like '%model%' and Name not like '%msdb%') or (ISNUMERIC(Right(SUBSTRING(name,1,charindex('.',name)-1),8))=0) or (Right(SUBSTRING(name,1,charindex('.',name)-1),8) > GETDATE()-1) or (Name like '%.trn')or (Name like '%_diff%')Sun, 25 Nov 2012 16:00:06 GMTsqlquery-101401http://www.sqlservercentral.com/Forums/Topic1384415-391-1.aspx[code="sql"]delete from #ExistingBackups where (Name not like '%master%' and Name not like '%model%' and Name not like '%msdb%') or (ISNUMERIC(Right(SUBSTRING(name,1,charindex('.',name)-1),8))=0) or (Right(SUBSTRING(name,1,charindex('.',name)-1),8) > '20121112')[/code]Mon, 19 Nov 2012 15:33:31 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1384415-391-1.aspxsome body suggested ,through select statement , but i was looking to make it simple where first delete statement results pass on to further delted , i can do that with simple 3 delete statements but looking from coding standard perspective what is the best way to do it?delete #ExistingBackups from( select * from #ExistingBackups where Right(SUBSTRING(name,1,charindex(''.'',name)-1),8) > ''20121111'') a left join ( select * from #ExistingBackups where Name like ''%master%'' and Name like ''%model%'' and Name like ''%msdb%'') b on a.ID=b.ID left join (select * from #ExistingBackups where ISNUMERIC(Right(SUBSTRING(name,1,charindex(''.'',name)-1),8))=0) c on a.ID=c.ID left join (select * from #ExistingBackups where Name like ''%_diff%'') d on a.ID=d.IDSun, 18 Nov 2012 17:31:33 GMTsqlquery-101401http://www.sqlservercentral.com/Forums/Topic1384415-391-1.aspxyes that is what i am looking for as i could not get pass through the same result OR clause , any idea how will it look like to get the same result?Wed, 14 Nov 2012 01:30:35 GMTsqlquery-101401http://www.sqlservercentral.com/Forums/Topic1384415-391-1.aspxYou can use OR ,CASE Statements !Wed, 14 Nov 2012 00:16:07 GMTyuvipoyhttp://www.sqlservercentral.com/Forums/Topic1384415-391-1.aspxi need to delete the records from a table with multiple conditions , like below first i am clearing non numeric records and then filtering system dbs records and then finally applying date filter delete, it looks ugly to me using many delete statements in the code , is there better way to write this in one delete statement to cover all the filters and achieve step3?step1:delete from #ExistingBackups where ISNUMERIC(Right(SUBSTRING(name,1,charindex('.',name)-1),8))=0step2:delete from #ExistingBackups where Name not like '%master%' and Name not like '%model%' and Name not like '%msdb%'step3:delete from #ExistingBackups where Right(SUBSTRING(name,1,charindex('.',name)-1),8) > '20121112'ThanksWed, 14 Nov 2012 00:06:18 GMTsqlquery-101401