Not with delete Statement help

Question

Post reply

Not with delete Statement help

asita

SSCarpal Tunnel

Points: 4144
More actions
August 2, 2021 at 6:48 am

#3916017

Hello There Good Morning,

Have a quick question, with delete statement right now I have the below statement. so i am trying to delete the other records that means not matching below criteria can i use NOT ?
Delete from Patient
WHERE (isnull(billamount,0)+isnull(discamount,0)<100
AND
(
domesticpatient = 'Y'
or
invalidaddress = 'Y'
or
invalid SSN = 'Y'
or
invalidpersonname = 'Y'
)
And servicetype = 'OP'

Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

asita SSCarpal Tunnel Points: 4144 More actions · Answer 1

Just to clarify,

in other words let say for example, we have this statement DELETE from Students where CalssName = 'Algebra'

would like to remove all other than algebra so inthis case we can change it to

DELETE from Students where CalssName <> 'Algebra'

but in the above example (first post) we have multiple conditions with OR clause so checking to see if i can use NOT () for whole where clause ? or not exists can you please provide some example if it is with not exists

Thank you

ScottPletcher SSC Guru Points: 101096 More actions · Answer 2

Yes, you should be able to apply NOT to the conditions to get the opposite of the current conditions.

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".

asita SSCarpal Tunnel Points: 4144 More actions · Answer 3

Hello SCott so you mean the below bold one additon?

Delete from Patient

WHERE

NOT(

(isnull(billamount,0)+isnull(discamount,0)<100

AND

(

domesticpatient = 'Y'

or

invalidaddress = 'Y'

or

invalid SSN = 'Y'

or

invalidpersonname = 'Y'

)

And servicetype = 'OP'

)

Thank you

Asiti

ScottPletcher SSC Guru Points: 101096 More actions · Answer 4

Yep, exactly.

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".

GaryV Hall of Fame Points: 3307 More actions · Answer 5

Alternatively, you can do the logic arithmetic yourself. Change the AND's to OR's, equals to not equals, etc. But that can be tricky, and oftentimes leads to code that is hard for humans to understand.