November 14, 2012 at 12:06 am
i 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))=0
step2:
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'
Thanks
November 14, 2012 at 12:16 am
You can use OR ,CASE Statements !
November 14, 2012 at 1:30 am
yes 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?
November 18, 2012 at 5:31 pm
some 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.ID
November 19, 2012 at 3:33 pm
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')
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 25, 2012 at 4:00 pm
Thanks ,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 backupsfrom 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%')
November 26, 2012 at 1:01 am
sqlquery-101401 (11/25/2012)
delete from #ExistingBackupswhere
(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%')
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 scene
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply