This OR this AND this - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

This OR this AND this

Author

Topic

NervousRex
Starting Member

9 Posts

Posted - 2011-06-15 : 11:53:05

This is so simple, I think I'm just over looking the obvious.

I'm making a select (that will eventually be a delete to KEEP the 1s, 4s (any date) AND 9s that are within a year)...so this select should be the records I want to delete eventually...

SELECT *
FROM dbo.PKA_Delete_Log
WHERE ((Keycode_ID = 9 AND Modify_Date < DATEADD(YEAR, -1, GETDATE()))
OR Keycode_ID NOT IN (1, 4))
AND Biz_ID = 793
AND Call_Type_ID = 5

Table
1 793 5 1/1/2010
4 793 5 1/1/2010
9 793 5 1/1/2010
9 793 5 1/1/2011
8 793 5 1/1/2010
6 793 5 1/1/2010

Expected
9 793 5 1/1/2010
8 793 5 1/1/2010
6 793 5 1/1/2010

Getting
9 793 5 1/1/2011
9 793 5 1/1/2010
8 793 5 1/1/2010
6 793 5 1/1/2010

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-15 : 12:55:18

SELECT *
FROM dbo.PKA_Delete_Log
WHERE Biz_ID = 793
AND Call_Type_ID = 5
(
(Keycode_ID = 9 AND Modify_Date < DATEADD(YEAR, -1, GETDATE()))
OR (Keycode_ID NOT IN (1, 4, 9) --got to add 9 because it's already been handled...
)

Corey

I Has Returned!!

NervousRex
Starting Member

9 Posts

Posted - 2011-06-15 : 13:11:25

Grrr, I swear I tried that along the way.

Thanks though!

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-06-15 : 16:47:38

If this is going to be put into a stored procedure - do you really need to do the delete in a single statement?

You could always wrap the deletes in a transaction and perform multiple delete statements if this has to be done as a unit. And, if this is a large number of rows - it is much easier to wrap each one in a batch delete.

Just a thought,

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources