Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dependent Delete statements Expand / Collapse
Author
Message
Posted Wednesday, November 14, 2012 12:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 8:04 AM
Points: 297, 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



Post #1384415
Posted Wednesday, November 14, 2012 12:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
You can use OR ,CASE Statements !
Post #1384417
Posted Wednesday, November 14, 2012 1:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 8:04 AM
Points: 297, 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?


Post #1384450
Posted Sunday, November 18, 2012 5:31 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 8:04 AM
Points: 297, 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



Post #1386113
Posted Monday, November 19, 2012 3:33 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:27 PM
Points: 2,127, Visits: 3,216

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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1386614
Posted Sunday, November 25, 2012 4:00 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 8:04 AM
Points: 297, 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%')



Post #1388419
Posted Monday, November 26, 2012 1:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:01 AM
Points: 2,840, Visits: 3,968
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
Post #1388458
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse