Where with two AND

Question

I'm Trying to delete rows from database that don't have phone, chellphone, and email (all 3 together)

DELETE * 
FROM TEST
WHERE (((TEST.EMAIL1) IS Null)
AND ((TEST.PHONE3) IS Null)
AND ((TEST.PHONE1) IS Null));

For some reason if I'm doing only any two (email1, phone1 or phone3) it works, but when I'm adding second 'AND' it stop working. Any advise please.

Answer 1

DELETE *
FROM test
WHERE (((test.EMAIL1) ="") OR ((test.EMAIL1) IS NULL))
AND (((test.PHONE1) = "") OR ((test.phone1) IS NULL))
AND (((test.PHONE3) ="") OR ((test.phone3) IS NULL));

Worked for me. Thanks to all..

Answer 2

You likely have no rows where all three are null. Check your data.

Answer 3

With all ANDs the parenthesis is not really necessary

DELETE
FROM TEST
WHERE TEST.EMAIL1 IS Null
AND TEST.PHONE3 IS Null
AND TEST.PHONE1 IS Null;

however, ensure that the values you expect to get deleted actually contain NULL and not something like empty string or the literal value of null.

You can check what information will be deleted by changing your statement to a select statement instead:

SELECT * 
FROM TEST
WHERE TEST.EMAIL1 IS Null
AND TEST.PHONE3 IS Null
AND TEST.PHONE1 IS Null;

Based on your own answer to your question you were running into empty strings, not nulls. Another way to write what you wrote and to avoid ORs would be:

SELECT * 
FROM TEST
WHERE isnull(TEST.EMAIL1, '') <> ''
AND isnull(TEST.PHONE3, '') <> ''
AND isnull(TEST.PHONE1, '') <> '';

In the above we're stating that any null test.email1s we encounter, treat as an empty string then check that that values is not an empty string.

So basically - if any of those three fields are null OR empty string. Same as your answer, just another way to write it.

Answer 4

Honestly the WHERE clause looks okay in this example, but the '*' should be left out:

DELETE FROM TEST
    WHERE (((TEST.EMAIL1) IS Null)
        AND ((TEST.PHONE3) IS Null)
        AND ((TEST.PHONE1) IS Null));

If you have trouble after you delete the asterisk, re-copy-paste it back in, and we can see if there's a problem with parentheses or something. But the above bracketing looks okay (even if not necessary).

Answer 5

I'm not sure what is going on with your case but I've cleaned up your statement a bit, as you shouldn't need all those () as far as I know and DELETE doesn't require the * as it should delete anything that matches your criteria. Try it out and let me know!

DELETE
FROM TEST
WHERE EMAIL1 IS Null
AND PHONE3 IS Null
AND PHONE1 IS Null