UPDATE SQL some field with "except" Conditions

Question

I would like to UPDATE sql by using "except" Conditions but it's not working.

This is sql query that I have used:

UPDATE `LMS_EMP` SET `email`="XXX@xXX.com" WHERE NOT `org_abbr_code` IN (HR100, HR101, HR103);

Here are my concept:

1.If some of fields org_abbr_code have these values HR100 OR HR101 OR HR103, the email field will not change.

2.If another org_abbr_codevalues, the email field will change as expect.

Thank you very much for any suggestion.

**I've edited for readable.

I think you are missing single quotes around the string constants. — Gordon Linoff, Feb 27 '18 at 13:17
@user9111600 String values should always be within quotes. And for all below answers it is same `WHERE NOT column_name IN ('value1', 'value2')` and `WHERE column_name NOT IN ('value1', 'value2')` — Krishan Kumar Mourya, Feb 27 '18 at 13:34
@KrishanKumarMourya Thank you for clarify, I have test withthis code and it's working UPDATE `LMS_EMP`SET `email`="XXX@XX.com" WHERE `org_abbr_code` NOT IN ('HR100', 'HR101', 'HR103'); I think first time I have use backtick instead of Single quotes. — user9111600, Feb 27 '18 at 13:39

score 2 · Answer 1 · answered Feb 27 '18 at 13:22

2

Following query will be helpful to you,

UPDATE LMS_EMP SET email="XXX@xXX.com" WHERE org_abbr_code NOT IN ("HR100", "HR101", "HR103");

answered Feb 27 '18 at 13:22

score 2 · Answer 2 · answered Feb 27 '18 at 13:23

2

UPDATE LMS_EMP SET email="XXX@xXX.com" WHERE org_abbr_code NOT IN ("HR100","HR101", "HR103");

answered Feb 27 '18 at 13:23

Krunal Shah

Daniel E. · Accepted Answer · 2018-02-27T13:27:08.853

1

As Gordon Linoff said, don't forget the single quote :

UPDATE `LMS_EMP` 
SET `email`='XXX@xXX.com' 
WHERE `org_abbr_code` NOT IN ('HR100', 'HR101', 'HR103');

edited Feb 27 '18 at 13:27

answered Feb 27 '18 at 13:24

Daniel E.

score -1 · Answer 4 · answered Feb 27 '18 at 13:43

-1

Same query i have added below

UPDATE LMS_EMP SET email="XXX@xXX.com" WHERE org_abbr_code NOT IN ("HR100", "HR101", "HR103");

Normally you want to use double quotation while you are adding any thing in NOT IN or IN statement or else it will throw an error

answered Feb 27 '18 at 13:43

Aravindhan R

Thanks! @Aravindhan. I will keep in mind. – user9111600 Feb 27 '18 at 13:48
Don't it's not true : https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql – Daniel E. Feb 27 '18 at 13:50
@DanielE. In some software like teradate will not support single quotation for set of character. Single quotation will support for numbers . In my experience i am tell this thing and i have worked on above statement by using single quotation – Aravindhan R Feb 27 '18 at 13:54
It's software specific who doesn't follow the SQL specification a string should be Single Quote. And for example in ORACLE a double quote will bring you an error. – Daniel E. Feb 27 '18 at 13:55
@DanielE. Thanks! Daniel to share another knowledge. – user9111600 Feb 27 '18 at 13:58
@DanielE. As you have posted the query it throwing error for this user. He is already tried by suggestion based on gordon lifoff. It's not working for him – Aravindhan R Feb 27 '18 at 13:59
the misplaced "not" ;) – Daniel E. Feb 27 '18 at 14:00

4 Answers4