Update statement SQL with case statement

Question

how to update values for following condition

CREATE TABLE T 
  ( 
     C VARCHAR(10) 
  ); 

INSERT INTO T 
VALUES      ('0000'), 
            ('123456789'),
            ('A'), 
            (''),
            ('    '), 
            (NULL);

I need to leave the NULL values or values which has 0000 as is and update the remaining to 1234.

expected output as below:

I have tried with below statement but is there a better way to do this?

UPDATE T SET C=CASE WHEN C IS NULL then null
when C='0000' then '0000' else '1234' END

To keep the transaction size down, don't update a column to the same value as before. Use WHERE to only update to new values. — jarlh
– jarlh, Commented Nov 8, 2018 at 10:07

Yogesh Sharma · Accepted Answer · 2018-11-08 09:04:28Z

2

You don't need to use case expression use where clause instead :

UPDATE t
     SET C = '1234'
WHERE C IS NOT NULL OR C <> '0000';

answered Nov 8, 2018 at 9:04

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jarlh Over a year ago

Will update the '0000' as well. (Since '0000' is not null...)

Zaynul Abadin Tuhin · Accepted Answer · 2018-11-08 09:24:37Z

2

just use where clause ,you can try like below

update T
set C='1234'
where C<>'0000'

edited Nov 8, 2018 at 9:24

answered Nov 8, 2018 at 9:04

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

3 Comments

George Menoutis Over a year ago

!= is not an operator in t-sql

GSerg Over a year ago

@GeorgeMenoutis It is.

jarlh Over a year ago

Will update the '0000' as well. (Since '0000' is not null...)

jarlh · Accepted Answer · 2018-11-08 10:08:23Z

1

Simply

UPDATE T SET C='1234' where c <> '0000'

Will work since the null comparisons aren't true.

SQL>UPDATE T SET C='1234' where c <> '0000';

                  4 rows updated

SQL>select * from t;
C
==========
0000
1234
1234
1234
1234
-

                  6 rows found

edited Nov 8, 2018 at 10:08

answered Nov 8, 2018 at 9:16

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Collectives™ on Stack Overflow

Update statement SQL with case statement

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related