Dependent Delete statements

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Dependent Delete statements

Dependent Delete statements

Rate Topic

Display Mode

Topic Options

Author

Message

sqlquery-101401

Posted Wednesday, November 14, 2012 12:06 AM

SSC 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

yuvipoy

Posted Wednesday, November 14, 2012 12:16 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:06 AM
Points: 111, Visits: 516

You can use OR ,CASE Statements !

Post #1384417

sqlquery-101401

Posted Wednesday, November 14, 2012 1:30 AM

SSC 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

sqlquery-101401

Posted Sunday, November 18, 2012 5:31 PM

SSC 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

ScottPletcher

Posted Monday, November 19, 2012 3:33 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 1,324, Visits: 1,778

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)
One man with courage makes a majority. Andrew Jackson

Post #1386614

sqlquery-101401

Posted Sunday, November 25, 2012 4:00 PM

SSC 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

Bhuvnesh

Posted Monday, November 26, 2012 1:01 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1388458

« Prev Topic | Next Topic »

Permissions