Dependent Delete statements

  • 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

  • You can use OR ,CASE Statements !

  • 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?

  • 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

  • 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • 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%')

  • sqlquery-101401 (11/25/2012)


    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%')

    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