Not with delete Statement help

  • 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

  • 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

     

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

  • 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

  • Yep, exactly.

    SQL DBA,SQL Server MVP(07, 08, 09) 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."

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

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

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