SQL DELETE Statement not working

Can someone please  explain to me why this statement isn't working and correct?

--Delete climasfamily records not flagged
DELETE climasfamily
FROM climasfamily
WHERE (((HV_Exists)<>'Y') AND ((CustomerId) <> ''));
GO

Note: HV_Exists Datatype is NVarChar(1) and has values of either Y or NULL
           CustomerId Datatype is NVarChar(50) and has values of a numeric or NULL
SpaceCoastLifeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

purpleoakCommented:
have you tried.
DELETE FROM climasfamily
WHERE (((HV_Exists)<>'Y') AND ((CustomerId) <> '')))
0
Éric MoreauSenior .Net ConsultantCommented:
and to do with the NULLs?

maybe you can try (NULLs will be kept):
DELETE 
 FROM climasfamily
WHERE isnull(HV_Exists, 'Y') <>'Y' 
AND isnull(CustomerId, 0) <> 0

Open in new window

0
Éric MoreauSenior .Net ConsultantCommented:
and before doing a DELETE, it would be safer to do a SELECT * !
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

purpleoakCommented:
indeed, SELECT * FROM climasfamily
WHERE (((HV_Exists)<>'Y') AND ((CustomerId) <> '')))

then you can see what you would delete with

DELETE FROM climasfamily
WHERE (((HV_Exists)<>'Y') AND ((CustomerId) <> '')))
0
SpaceCoastLifeAuthor Commented:
Staying with the SELECT statement instead of DELETE, I tried every variation suggested to no avail.

SELECT * FROM climasfamily
 WHERE (((HV_Exists)<>'Y') AND ((CustomerId) <> '')))

If I try HV_Exists = 'Y' instead, records are returned but of course not what I need - and yes, I've verified there are several hundred records where the HV_Exists value is not 'Y' (Null) and the CustomerId value is not null.

Any other ideas?
0
Éric MoreauSenior .Net ConsultantCommented:
NULL is not ''. you need to handle them correctly

have you tried
SELECT * FROM climasfamily
WHERE (HV_Exists is not null and HV_Exists <>'Y') AND CustomerId is not null
0
purpleoakCommented:
what id the error message you get in the sql window?

Moreau is correct if looking for nulls and not empty strings you need to handle nulls correctly.
what is the result of the following?

SELECT * FROM climasfamily
WHERE (HV_Exists<>'Y') AND (CustomerId is not null)
0
SpaceCoastLifeAuthor Commented:
No error. Message says Query  Executed Successfully.

SELECT * FROM climasfamily
 WHERE (HV_Exists is not null and HV_Exists <>'Y') AND CustomerId is not null

Same result
0
Éric MoreauSenior .Net ConsultantCommented:
It just doesn't make sense!

I have re-read your question.

Note: HV_Exists Datatype is NVarChar(1) and has values of either Y or NULL
            CustomerId Datatype is NVarChar(50) and has values of a numeric or NULL

Can you try this:
SELECT * FROM climasfamily WHERE HV_Exists is null AND CustomerId is not null

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SpaceCoastLifeAuthor Commented:
Apologize for not closing this out earlier. I never figured it out but didn't have time to mess with it anymore so I forced a vale in HV_Exisis and dumped the Null.

Thanks for the help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.