SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dependent Delete statements


Dependent Delete statements

Author
Message
sqlquery-101401
sqlquery-101401
SSC-Addicted
SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)

Group: General Forum Members
Points: 413 Visits: 354
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



yuvipoy
yuvipoy
Right there with Babe
Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)

Group: General Forum Members
Points: 790 Visits: 1386
You can use OR ,CASE Statements !
sqlquery-101401
sqlquery-101401
SSC-Addicted
SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)

Group: General Forum Members
Points: 413 Visits: 354
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?



sqlquery-101401
sqlquery-101401
SSC-Addicted
SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)

Group: General Forum Members
Points: 413 Visits: 354
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



ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7825 Visits: 7145

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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
sqlquery-101401
sqlquery-101401
SSC-Addicted
SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)

Group: General Forum Members
Points: 413 Visits: 354
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 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%')



Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5202 Visits: 4076
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;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search